June 21, 2016 at 3:12 pm
Hi,
I need to pull the unique records for a field from a table based on changes.
In the a table, there can be multiple entries for same value, but if the value is changes , I need to pull that.
Here is the eg : i have main table , i need to pull all the color records base don changes.
Main Table
IDColorVehicleDate
100RedCar6/17/2016
99GreenCar6/16/2016
98BlueBike6/15/2016
97BlueBike6/14/2016
96BlueBike6/13/2016
95BlueBike6/12/2016
94BlueBike6/11/2016
93BlueBike6/10/2016
92BlueBike6/9/2016
91BlueBike6/8/2016
90BlueBike6/7/2016
89Redtruck6/6/2016
88Greentruck6/5/2016
87Greentruck6/4/2016
i need the entries for all change, in main table ID 88 is green to red in ID 89
and ID 98 is blue to Green, in that case i need the ids 88,99 for green
the result set would be something like this.
SQL Query :Distinct Color ch
IDColordate
100Red6/17/2016
99Green6/16/2016
98Blue6/15/2016
89Red6/6/2016
88Green6/5/2016
is it possible do it in a SQL query, any help is appreciated.
June 22, 2016 at 7:11 am
Sure this is possible. In the future it would be helpful if you could post the table and sample data in a consumable format. Since I see you are new around here I put this together for you so you can see how this can be done.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
ID int
, Color varchar(10)
, Vehicle varchar(10)
, ChangeDate Date
)
insert #Something(ID, Color, Vehicle, ChangeDate) values
(100, 'Red', 'Car', '6/17/2016'),
(99, 'Green', 'Car', '6/16/2016'),
(98, 'Blue', 'Bike', '6/15/2016'),
(97, 'Blue', 'Bike', '6/14/2016'),
(96, 'Blue', 'Bike', '6/13/2016'),
(95, 'Blue', 'Bike', '6/12/2016'),
(94, 'Blue', 'Bike', '6/11/2016'),
(93, 'Blue', 'Bike', '6/10/2016'),
(92, 'Blue', 'Bike', '6/9/2016'),
(91, 'Blue', 'Bike', '6/8/2016'),
(90, 'Blue', 'Bike', '6/7/2016'),
(89, 'Red', 'truck', '6/6/2016'),
(88, 'Green', 'truck', '6/5/2016'),
(87, 'Green', 'truck', '6/4/2016');
Now we all have the same table to work with so all you need is the query to get your results. We can leverage the ROW_NUMBER window function for this quite easily. Since we only want to get rows for a certain value of that window function I am using a cte because you can't put the window function in a where predicate in the same query.
with sortedResults as
(
select ID
, Color
, Vehicle
, ChangeDate
, ROW_NUMBER() over(Partition by Color, Vehicle order by ChangeDate desc) as RowNum
from #Something
)
select ID
, Color
, ChangeDate
from sortedResults
where RowNum = 1
order by ID desc
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2016 at 12:33 pm
thank you 🙂 it worked on bot SQL server and Db2.
June 22, 2016 at 12:55 pm
mastsetup (6/22/2016)
thank you 🙂 it worked on bot SQL server and Db2.
Glad that worked for you and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply