SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Yak Posting Veteran

71 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
47189 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

876 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
Yak Posting Veteran

71 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
47189 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000