Help with (probably simple) query

Question

Help with (probably simple) query

captcrotch

Grasshopper

Points: 19
More actions
June 25, 2010 at 3:24 am

#224240

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

captcrotch Grasshopper Points: 19 More actions · Answer 2

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Are there chances of one RelationId getting more than one value for xaml column ?

I almost did the query, but waiting for this confirmation..

LutzM SSC Guru Points: 107049 More actions · Answer 5

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

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

captcrotch Grasshopper Points: 19 More actions · Answer 6

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

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"

captcrotch Grasshopper Points: 19 More actions · Answer 8

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

captcrotch Grasshopper Points: 19 More actions · Answer 10

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 11

ChiragNS

One Orange Chip

Points: 26137

June 25, 2010 at 5:22 am

#1184918

Hi Imu92

Good solution.

"Keep Trying"

Help with (probably simple) query

Cookies on SQLServerCentral