June 25, 2010 at 3:24 am
Hi
Im sure theres a fairly simple way to do this but its beyond my simple sql knowledge...
I have a table with the following data (ordered by ProductId):
[font="Courier New"]
RelationId ProductId DateRelated Xaml
---------- --------- ----------- --------
1 lu231342 2010-06-24T16:26:04.493 NULL
3 lu231342 2010-06-24T16:25:26.643 <Columnist Key="h...
5 lu231342 2010-06-24T16:28:14.993 NULL
2 lu231343 2010-06-24T16:29:29.933 NULL
6 lu231343 2010-06-24T16:35:72.874 NULL
4 lu231344 2010-06-24T16:29:41.540 NULL
[/font]
all I want to do is get distinct rows based on ProductId, with the following logic:
- if the Xaml is non-null then that row should be returned
- otherwise the product row with the most recent DateRelated date should be returned
ie the query on the above data would return:
[font="Courier New"]
RelationId ProductId DateRelated Xaml
---------- --------- ----------- --------
3 lu231342 2010-06-24T16:25:26.643 <Columnist Key="h...
6 lu231343 2010-06-24T16:35:72.874 NULL
4 lu231344 2010-06-24T16:29:41.540 NULL
[/font]
Make sense? I know there must be a simple way to do this but my brain refuses to get it 🙂
(one final thing - Im hoping to put this query into a view, so any answers that are view-friendly are much appreciated)
tia
crotchy
June 25, 2010 at 3:46 am
captcrotch (6/25/2010)
[font="Courier New"]RelationId ProductId DateRelated Xaml
---------- --------- ----------- --------
3 lu231342 2010-06-24T16:25:26.643 <Columnist Key="h...
6 lu231343 2010-06-24T16:35:72.874 NULL
4 lu231344 2010-06-24T16:29:41.540 NULL
[/font]
Why would both 6 and 4 be returned if you want the most recent? Surely it'd just be 6? Well, if 6 was valid. You can't have 72 seconds in a minute 😛
DECLARE @table TABLE(
relationid INT IDENTITY,
productid VARCHAR(15),
daterelated DATETIME,
xaml VARCHAR(50))
INSERT INTO @table(ProductId, DateRelated, Xaml)
SELECT 'lu231342', '2010-06-24T16:26:04.493', NULL
UNION ALL SELECT 'lu231342', '2010-06-24T16:25:26.643', '<Columnist Key="h...'
UNION ALL SELECT 'lu231342', '2010-06-24T16:28:14.993', NULL
UNION ALL SELECT 'lu231343', '2010-06-24T16:29:29.933', NULL
--UNION ALL SELECT 'lu231343', '2010-06-24T16:35:72.874', NULL THIS VALUE IS INVALID AND COMES FROM YOUR SAMPLE
UNION ALL SELECT 'lu231344', '2010-06-24T16:29:41.540', NULL
June 25, 2010 at 3:55 am
well thanks for the response skcadavre but the data given is just example data, I need a general query to retrieve data following the logic I gave, whatever is in the table.
as for the 72, good spot - I just hand edited it in place
June 25, 2010 at 3:58 am
captcrotch (6/25/2010)
well thanks for the response skcadavre but the data given is just example data, I need a general query to retrieve data following the logic I gave, whatever is in the table.
That doesn't actually answer my question. . .
Why are both RelationId 4 and 6 expected to be returned if you want the most recent? Reading the logic you have mentioned, I'd expect to get: -
RelationId ProductId DateRelated Xaml
---------- --------- ----------- --------
3 lu231342 2010-06-24T16:25:26.643 <Columnist Key="h...
6 lu231343 2010-06-24T16:35:72.874 NULL
June 25, 2010 at 3:58 am
Are there chances of one RelationId getting more than one value for xaml column ?
I almost did the query, but waiting for this confirmation..
June 25, 2010 at 4:05 am
Here's my approach:
;WITH cte AS
(
SELECT
relationid,
ROW_NUMBER() OVER(PARTITION BY ProductId
ORDER BY CASE WHEN XAML IS NULL THEN 2 ELSE 1 END, DateRelated DESC) AS ROW
FROM @table
)
SELECT t.*
FROM @table t
INNER JOIN cte ON t.relationid = cte.relationid
WHERE cte.row=1
June 25, 2010 at 4:09 am
skcadavre (6/25/2010)
captcrotch (6/25/2010)
well thanks for the response skcadavre but the data given is just example data, I need a general query to retrieve data following the logic I gave, whatever is in the table.That doesn't actually answer my question. . .
Why are both RelationId 4 and 6 expected to be returned if you want the most recent? Reading the logic you have mentioned, I'd expect to get: -
RelationId ProductId DateRelated Xaml
---------- --------- ----------- --------
3 lu231342 2010-06-24T16:25:26.643 <Columnist Key="h...
6 lu231343 2010-06-24T16:35:72.874 NULL
Apologies for not being clear - Im after all products but only 1 row for each, based on the logic. ie the most recent row for a given product id should be returned unless the xaml is non-null.
and there will only ever be a single xaml entry per product id, but there can be multiple null-xaml entries per product id.
Hope that makes sense 🙂
June 25, 2010 at 4:09 am
Hi
I assume that only one record for a productid will have value in xaml column. Noticed it when the previous poster put that point accross. Here is my query and i am sure there are other more elegant ways of doing it but right now my mind can come up with nothing better...
DECLARE @table TABLE(
relationid INT IDENTITY,
productid VARCHAR(15),
daterelated DATETIME,
xaml VARCHAR(50))
INSERT INTO @table(ProductId, DateRelated, Xaml)
SELECT 'lu231342', '2010-06-24T16:26:04.493', NULL
UNION ALL SELECT 'lu231342', '2010-06-24T16:25:26.643', '<Columnist Key="h...'
UNION ALL SELECT 'lu231342', '2010-06-24T16:28:14.993', NULL
UNION ALL SELECT 'lu231343', '2010-06-24T16:29:29.933', NULL
--UNION ALL SELECT 'lu231343', '2010-06-24T16:35:72.874', NULL THIS VALUE IS INVALID AND COMES FROM YOUR SAMPLE
UNION ALL SELECT 'lu231344', '2010-06-24T16:29:41.540', NULL
--select * from @table
select * from @table where xaml is not null
union all
select t.* from @table t inner join (
select max(daterelated) daterelated,productid from @table
where productid not in (select productid from @table where xaml is not null )
group by productid ) A
on t.productid = A.productid and t.daterelated = A.daterelated
"Keep Trying"
June 25, 2010 at 4:10 am
skcadavre (6/25/2010)
captcrotch (6/25/2010)
well thanks for the response skcadavre but the data given is just example data, I need a general query to retrieve data following the logic I gave, whatever is in the table.That doesn't actually answer my question. . .
Why are both RelationId 4 and 6 expected to be returned if you want the most recent? Reading the logic you have mentioned, I'd expect to get: -
RelationId ProductId DateRelated Xaml
---------- --------- ----------- --------
3 lu231342 2010-06-24T16:25:26.643 <Columnist Key="h...
6 lu231343 2010-06-24T16:35:72.874 NULL
RelationId is unique PK
June 25, 2010 at 4:39 am
Use lmu92 query, it will even work in case if you will have more than one record with non-null Xaml.
If you like, you can add every column into CTE, so you will not need to join it back to the table for the final results:
;with cteP
as
(
select RelationId, ProductId, DateRelated, Xaml, ROW_NUMBER() OVER(partition by ProductId order by case when Xaml is null then 2 else 1 end, DateRelated desc) as rn
from @table
)
select RelationId, ProductId, DateRelated, Xaml
from cteP
where rn = 1
June 25, 2010 at 4:50 am
Many thanks guys - you lot certainly know your stuff
I'd thought maybe I was missing a simpler way to do it but it looks like it wouldve been beyond my skills anyway.
thanks again
June 25, 2010 at 5:22 am
Hi Imu92
Good solution.
"Keep Trying"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply