Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with grouping our Distinct

Author  Topic 

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-12 : 01:05:48
I Have a query I need to either disticnt or group I cant get either to work
Normal query below

SELECT TOP 10 [UserLogin]
,[comm_id]
,[CaseID]
,[Creation_Date]
,[Supervisor]
,[YearWeek]
FROM Correspondences_All where userlogin = 'blah'


it returns:


UserLogin comm_id CaseID Creation_Date Supervisor YearWeek
Blah 24584076055 1283974401 12:42.0 Supervisor1 201307
Blah 24552235745 1283974401 14:20.0 Supervisor1 201306
Blah 24556867065 1283974401 03:18.0 Supervisor1 201306
Blah 24648408055 1283974401 11:54.0 Supervisor1 201309
Blah 24531485005 1484627711 36:24.0 Supervisor1 201305
Blah 24522586555 1484627711 24:52.0 Supervisor1 201305
Blah 24527107785 1484627711 58:30.0 Supervisor1 201305
Blah 24528286905 7684732591 00:04.0 Supervisor1 201305
Blah 24560995615 1285109231 37:14.0 Supervisor1 201306
Blah 24561807655 1285109231 15:41.0 Supervisor1 201306


I need something like this:

UserLogin comm_id CaseID Creation_Date Supervisor YearWeek
Blah 24584076055 1283974401 12:42.0 Supervisor1 201307
Blah 24531485005 1484627711 36:24.0 Supervisor1 201305
Blah 24528286905 7684732591 00:04.0 Supervisor1 201305
Blah 24560995615 1285109231 37:14.0 Supervisor1 201306


Note that I am doing this at the CasedID level. I can go this no problems in mySQL using a group by but SQL server is not so kind

MYSQL =
SELECT TOP 10 [UserLogin]
,[comm_id]
,[CaseID]
,[Creation_Date]
,[Supervisor]
,[YearWeek]
FROM Correspondences_All where userlogin = 'blah'
order by caseID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 01:08:01
[code]
SELECT [UserLogin]
,[comm_id]
,[CaseID]
,[Creation_Date]
,[Supervisor]
,[YearWeek]
FROM
(
SELECT [UserLogin]
,[comm_id]
,[CaseID]
,[Creation_Date]
,[Supervisor]
,[YearWeek]
,ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY comm_id ASC) AS Seq
FROM [AWS_Cases].[dbo].[Correspondences_All] where userlogin = 'blah'
)t
WHERE Seq =1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-12 : 01:28:31
or something a bit more old-school:

SELECT [UserLogin]
,[comm_id]
,[CaseID]
,[Creation_Date]
,[Supervisor]
,[YearWeek]
FROM Correspondences_All a inner join
(
SELECT [CaseUD], min(comm_id) comm_id
FROM [AWS_Cases].[dbo].[Correspondences_All] where userlogin = 'blah'
)t
On t.comm_id=a.comm_id

Check your query plan to see what works best.
Go to Top of Page

2revup
Posting Yak Master

112 Posts

Posted - 2013-03-12 : 01:41:17
Thanks guys appreciate the quick responses, working like a charm!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 01:44:18
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -