Author |
Topic |
shailesh18
Starting Member
8 Posts |
Posted - 2011-10-01 : 04:26:07
|
HiI have an table of 500,000 records.the data in table is like FE------------------------------------ID CID Cdt_DT (dd-MM-yy)-------------------------------------1 1 01-01-111 2 05-03-112 1 08-01-112 5 18-06-11-------------------------------------- I want only unique values of ID column from this table with date sorted ascending or descending. how can i achieve this.Here is sample output------------------------------------ID CID Cdt_DT (dd-MM-yy)-------------------------------------1 2 05-03-112 5 18-06-11------------------------------------- Thanks in advance |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-10-01 : 08:07:00
|
Using row_number function would perhaps be the simplest:;WITH cte AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY cdt_DT DESC) RN FROM FE)SELECT ID, CID, Cdt_DT FROM cte WHERE RN = 1; |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-02 : 07:28:41
|
hi ,for my understanding , you want unique values of colum ID and CID you can try :SELECT DISTINCT ID, CIDFROM tablename |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-02 : 07:43:32
|
quote: Originally posted by paultech hi ,for my understanding , you want unique values of colum ID and CID you can try :SELECT DISTINCT ID, CIDFROM tablenamegood luck,paul Tech
Can you please test it before you post something?how do you think above suggestion will give posted output for OP?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vmvadivel
Yak Posting Veteran
69 Posts |
Posted - 2011-10-02 : 12:16:44
|
I just tweaked Paul's response.SELECT DISTINCT ID AS [ID], MAX(cid) AS [CID], CONVERT(VARCHAR(10), MAX(cdt_dt), 105) AS [Cdt_DT (dd-MM-yy)]FROM FEGROUP BY IDGOBest RegardsVadivelhttp://vadivel.blogspot.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-02 : 13:41:54
|
quote: Originally posted by vmvadivel I just tweaked Paul's response.SELECT DISTINCT ID AS [ID], MAX(cid) AS [CID], CONVERT(VARCHAR(10), MAX(cdt_dt), 105) AS [Cdt_DT (dd-MM-yy)]FROM FEGROUP BY IDGOBest RegardsVadivelhttp://vadivel.blogspot.com
this will work for sample data postedBut OP should see if date values go on increasing trend as per CID value else this wont work.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vmvadivel
Yak Posting Veteran
69 Posts |
Posted - 2011-10-02 : 21:52:03
|
Yep I just tweaked his response to fit the sample data provided. As his query in current form wouldn't show up anything. BTW what does OP mean?Best RegardsVadivelhttp://vadivel.blogspot.com |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-02 : 22:24:05
|
OP = Original Poster KH[spoiler]Time is always against us[/spoiler] |
|
|
vmvadivel
Yak Posting Veteran
69 Posts |
Posted - 2011-10-03 : 03:09:02
|
Thanks Khtan .. I learned something today :)Best RegardsVadivelhttp://vadivel.blogspot.com |
|
|
shailesh18
Starting Member
8 Posts |
Posted - 2011-10-03 : 04:04:02
|
Hi sunitathats the perfect solution.quote: Originally posted by sunitabeck Using row_number function would perhaps be the simplest:;WITH cte AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY cdt_DT DESC) RN FROM FE)SELECT ID, CID, Cdt_DT FROM cte WHERE RN = 1;
|
|
|
shailesh18
Starting Member
8 Posts |
Posted - 2011-10-03 : 04:04:32
|
Thanks everybody for all your efforts |
|
|
|