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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with grouping our Distinct
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

112 Posts

Posted - 03/12/2013 :  01:05:48  Show Profile  Reply with Quote
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


Edited by - 2revup on 03/12/2013 01:06:48

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/12/2013 :  01:08:01  Show Profile  Reply with Quote

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


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

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/12/2013 :  01:28:31  Show Profile  Reply with Quote
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 - 03/12/2013 :  01:41:17  Show Profile  Reply with Quote
Thanks guys appreciate the quick responses, working like a charm!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/12/2013 :  01:44:18  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000