Sort / Group?

Sep 21, 2000

I have a table that most of the data has the same value, but there are only a few that do not match that value. I want to populate a listbox with all values from the table, but I'd like to have the majority listed first, followed by the others (the few that don't matach). What's the best way to approach this with SQL?

Thanks.

View 3 Replies


ADVERTISEMENT

Sort/Group Need Help

Nov 7, 2007

need some sql assistance....have this:
Model Date
A 2/2/07
B 1/2/07
C 1/1/07
A 3/1/07
C 2/2/07

I think you get the idea....random.... I need sorted by Model that's important - but then with lowest date first:

C 1/1/07
C 2/2/07
B 1/2/07
A 2/2/07
A 3/1/07

have the ability to create a control column if that helps...but am struggling with the sql statement that will give me the correct results and would welcome input....thanks in advance...

View 8 Replies View Related

Sort And Group

Jan 11, 2008

Hi All,

Running SQL 2005

DB as a colum that contains hundreds of file types ie, mp3,wav,txt,doc,pdf, etc

When i run the folowing

use DB

go

select file1

from dbo.DB

where file1 like '%'

I get back (4192103 row(s) affected)

What would run to get back how many mp3's,docs'txt files there are?

Kind Regards

Leeds

View 20 Replies View Related

Top N Sort On Group

Mar 3, 2008

I am doing a running 12 month Sales report. Invoice totals are summed. I want to Group By Location and Customer. I would also like to do a Top 20 customers for each location. Here is what I have so far and it comes back with a failure of Invalid column name 'Location'.

I don't know how to do the Top 20 either. Any help would be appreciated. Thanks.


SELECT SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 2 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 3 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 4 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 5 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 6 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 7 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 8 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 9 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 10 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END) AS LastYearTotal,
SUM(CASE WHEN DATEADD([year], - 1, GetDate()) > ih.SOTransDate AND DATEDIFF([YEAR], ih.SOTransDate, GetDate())
= 1 THEN id.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
CASE WHEN a.Account LIKE '400%' THEN 'ALEDO' ELSE ' ' END AS Location,
ih.SOTransDate, ih.InvoiceNumber, cm.CustomerName, id.DetailSeqNumber, id.LineType, a.Account, id.SOExtChargeAmount
FROM dbo.MAS_CCS_AR1_CustomerMaster cm, dbo.MAS_CCS_ARN_InvHistoryHeader ih, dbo.MAS_CCS_ARO_InvHistoryDetail id,
dbo.MAS_CCS_GL_Account a
WHERE cm.CustomerNumber = ih.CustomerNumber AND ih.InvoiceNumber = id.InvoiceNumber AND id.SOGLSalesAcct = a.AccountKey
GROUP BY cm.CustomerName, Location
ORDER BY CurrentYearTotal

View 3 Replies View Related

Sort On Group Total: How?

May 18, 2006

How do I sort on a group total item?

View 6 Replies View Related

How To Apply Interactive Sort On Tables With Group?????

Feb 7, 2008

In my Report, i have a grouping on project and Drill down on Project Cloumn only,now i need to implement interactive sort on rest of the columns,i implemented But it seems not working. can any one help me ..................... It is urgent requirement for me. rgds, kousik.

View 7 Replies View Related

Manualy Sort Matrix Column Group

Nov 7, 2007

I've done this before, but i cant remember how.

All i want to do is sort this group in a specific way.
I want them to be in this order:


AME01

ASE01

ACO01

ALU01

AOS01

APH01

ATR01

ATE01

ACR06

ACR05

ACR02

ACR03

ACR08

ACR07
What is the code i would put in the sorting expression, for this to take place?

View 3 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View 4 Replies View Related

Dynamic Sort Column And Sort Order Not Working

Aug 7, 2007

I am trying to set sorting up on a DataGrid in ASP.NET 2.0.  I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure:   CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC  And I gotta tell you, that is ugly code, in my opinion.  What I am trying to do is something like this:  case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end

case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end  and it's not working at all, i get an error saying:  Incorrect syntax near the keyword 'case' when i put a comma after the end on line  5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak 

View 1 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?

Nov 27, 2007

Hi!

I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679

Provide Individual Page Numbering per Group and Total Pages per Group

Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.


I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.


Thanks!
Karen

View 1 Replies View Related

Reporting Services :: RDL XML To Unmerge Cells In Group BUT Keep Group Drill-In In Excel

Nov 6, 2015

I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this. 

View 4 Replies View Related

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Error 15401: Windows NT Group Or Group Not Found

Sep 25, 2003

I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager

Now when I try to add Mikel, Im getting error 15401.

Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?

Can I go into the Master database and just change Mike login to Mikel ?

Thank you

View 3 Replies View Related

SQL 2012 :: SSRS Average Column Group Value For Row Group

Feb 28, 2014

I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:

Year
Month
Date

And the following row groups:

Region
Product
SubType (hidden, data at the date level is summed to Product)

At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)

View 0 Replies View Related

SQL 2012 :: Fast Way To Do Group By Count On Items Within Group?

May 1, 2014

select top 15 count(*) as cnt, state from table
group by state
order by cnt desc

[code[...

Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.

View 9 Replies View Related

Any Way To Show A Group Detail Header Row Once For Each Group In A Table?

Nov 21, 2007

I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.

View 1 Replies View Related

Login For Domain Local Group And Global Group

Jan 5, 2008

I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.

Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.

Why does SQL server 2005 has this limitation? Is it a bug?

View 1 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View 1 Replies View Related

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:

Code:


SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date



The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related

How To Use Group By (group Tasks Based On Projects)

May 28, 2007

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:







Project1-------------------->task1

task2

task3

task4



Project2----------------------->task4

task5

task6





.............................................projectN.....................





how to write query for this



i have 2 tables:

Project .......>columns are projectid

Task------------->columns are projectid, taskid

|


View 4 Replies View Related

Repeat First Row Group Header For Second Row Group Items

Jun 6, 2007

I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.



Example:

Now:




Code Snippet
Column Column
Section1 -

Mnemonic

Mnemonic

Mnemonic

Mnemonic

Section2 -

Mnemonic

Mnemonic

Mnemonic

Mnemonic





Should be:


Code Snippet

Column Column

Section1 -

Section1 Mnemonic

Section1 Mnemonic

Section1 Mnemonic

Section2 -

Section2 Mnemonic

Section2 Mnemonic

Section2 Mnemonic

View 2 Replies View Related

Not A Single-group Group Function

Feb 12, 2015

I have a code like this:

SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation

[code]....

So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....

View 1 Replies View Related

T-SQL Problem...selecting TOP 1 Of Each GROUP In GROUP BY?

Jul 23, 2005

Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks

View 6 Replies View Related

Adding SubTotol Of A Group To Group

Sep 6, 2007

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000


For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000
2 a 100 2000 0
Team Total 100 0
Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)


I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount
And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0
2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)


this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ??
thank you

View 9 Replies View Related

Recovery :: Migrating Existing Availability Group To New Datacenter Availability Group

Nov 20, 2015

need to migrate a cluster with an AG dtabases to new data center cluster with AG.

I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.

View 17 Replies View Related

DB Design :: Partition With Single File Group Or Multiple File Group?

May 19, 2015

partition with single file group or multiple file group which one best.

we have some report running from partition table, few reports don't have any partition Key and after creating 400  partition  with 400 file group it is slow.what is best practices to crate  400 file group or single file group.

View 9 Replies View Related

Cannot Sort A Row

Oct 26, 2006

i encounter this error..

Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?

why this error occur? can someone explain? how to avoid this? thanks!!

View 1 Replies View Related

To Sort Or Not To Sort

Dec 10, 2007

We are using a modeling technique called Anchor Modeling in our data warehouses. You can read more about the technique itself at our homepage http://www.intellibis.se, where we have published a fact sheet and a recently held presentation (TDWI European conference). One of the features with this technique is its simple way to historize data. This is done by having a fromDate column which together with the surrogate key will yield a unique combination. On the tables that has this kind of historization we add a primary key, which in turn will create a clustered index, with the following specification (surrogateKey asc, fromDate desc). This will physically order data on the storage media according to the specificed columns and ordering. Now I move on to create a "latest view" of this table which does a subselect to find the latest version for every surrogateKey using max(fromDate). Should not the optimizer now figure out that data is ordered so that the latest version always comes first for every surrogateKey, hence any sorting would be unneccessary? If I look at the actual execution plan after running a query that uses the view there is a sort in the plan, but the cost is always 0%. Does this mean that it did not sort the data, or that it did call a sorting routine, but it actually took very little time to do the sorting? If so, is there a reason that is has to do the sorting or could it have been left out by an even smarter optimizer?

I would also like to applaud the people behind the optimizer, since it will figure out which tables are in fact necessary to query and eliminate others, even if I have left joined them into the view I am using. This speeds up performance and makes anchor modeling feasible. Unfortunately optimizers from other vendors seem to have trouble doing this...

Regards,
Lars

View 2 Replies View Related

SQL Sort Help

Mar 11, 2008

I've been racking my brain all day and I finally decided to ask for help. I've got two tables with rows from the first that need to be sorted by the second. The problem is that the rows don't always exist in the second table. I've tried various forms of INNER, LEFT, RIGHT, OUTER, LEFT OUTER, CROSS, etc., etc., etc. and nothing (oh yeah UNION too). Every time I get close, I lose the records that don't have matches.

Something close-

SELECT A.IDDoc, B.First
FROM A
LEFT JOIN B
ON A.IDDoc = B.IDDoc
WHERE B.Dept = 'A'
ORDER BY B.First

Example Data

Table A
IDDoc Document
---------------------------
1 1467.doc
2 8722.doc
3 A47F.doc
4 88DQ.doc
5 ABCD.doc

Table B
IDDoc Dept First
----------------------------
1 A John
2 A Bob
3 A Ralph
4 A Diane

Results I Want
IDDoc First
-------------------
5 NULL
2 Bob
4 Diane
1 John
3 Ralph


Any help is appreciated. If I've posted in the wrong forum, please feel free to direct me to a better one.

Thanks in advance!

Jim

View 3 Replies View Related

How To Sort The Date ?

Oct 12, 2006

SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,SUM(CASE  a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,SUM(CASE  a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,SUM(CASE  a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,SUM(CASE  a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,SUM(CASE  a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,SUM(CASE  a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as OthersFrom
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006' GROUP BYLEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)Output :Aug 2006  294       0    0    80      0       0    Jan 2006    14        0    0    0      0         0    Oct 2006  336       0    0    0        0       0    Sep 2006  3262    20    24    8    16    0    How to sort the date in ascending Order ?Jan 2006Aug 2006Sep 2006Oct 2006

View 2 Replies View Related

Sort Of Intersect

Feb 27, 2008

I have: 4 tables and 1 table variable.
CCenters (ID, Name)
Campaigns (ID, Name)
Rel (ID, CCenterID, CampaignID) - [many to many]
and @SCampaigns (ID, CampaignID) - represents the selected campaigns by the user

performing the commands below I would get the centers associated with the campaigns selected.SELECT CCenterID
FROM Rel
INNER JOIN @Campaigns ON @SCampaigns.CampaignID = Rel.CampaignID
 But what I really want are the common centers to the selected campaigns.
 Thanks

View 1 Replies View Related

Select Sort

Mar 18, 2004

I am trying to select a record from a table where it has the smallest priority
how would you go about doing this
is there a cool sort command or is there a select command syntax that can do this
thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved