SQL, "DISTINCT", Left Join and Row_Number!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL, "DISTINCT", Left Join and Row_Number!

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-27 : 13:59:05

Hi, and help!

I need a SQL query for paging but don't want to go down the ADO paging route atm. I need the query to do what the 1st below does but where I can specify ROW_NUMBER() between x and y AND it also needs to select unique/distinct records by Otbl.ID - which is NOT the primary key for the Otbl. I'm just not sure how to put it together...

SELECT TOP 20 Oftbl.ID, Mtbl.Name, Mtbl.URL, ROW_NUMBER() OVER (PARTITION BY Mtbl.name ORDER BY Mtbl.name) FROM Oftbl LEFT JOIN Mtbl ON Oftbl.ID=Mtbl.ID WHERE Oftbl.[end_date] >= GetDate() AND [code] IS NOT NULL ORDER BY Oftbl.id;

The query I use for getting "distinct/unique" records:

SELECT TOP 20 Oftbl.Name, Oftbl.Desc FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Oftbl.Name ORDER BY Oftbl.Name) AS RowNo, Oftbl.Name, Oftbl.Desc FROM Oftbl WHERE Oftbl.[end_date] >= DATEADD(day,-5,GETDATE()) AND Oftbl.[Value] = 'Yes' )Oftbl WHERE Oftbl.RowNo=1.

I can get some sample and required result data if needed.

Appreciate help with this. ( SQL 2008 )

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-27 : 14:33:52

Peter can you post some sample input data and corresponding output? I read your posting couple of times, and I have a vague understanding of what you need, but sample data would help a lot.

If there is someone else reading this forum who is able to understand the requirements and respond without sample data, I am sure having sample data will make it easier for them.

Sachin.Nand

2937 Posts

Posted - 2011-03-27 : 14:59:46

This?


SELECT TOP 20 Oftbl.Name, Oftbl.Desc FROM
 ( SELECT ROW_NUMBER() OVER (PARTITION BY Oftbl.Name ORDER BY Oftbl.Name) AS RowNo, Oftbl.Name, Oftbl.Desc FROM Oftbl WHERE Oftbl.[end_date] >= DATEADD(day,-5,GETDATE()) AND Oftbl.[Value] = 'Yes' )
 Oftbl WHERE Oftbl.RowNo=1 and(RowNo between X and Y)

Not sure why are you using TOP 20

PBUH

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-27 : 15:30:40

@Sunita..Here's some basic sample data. ( Cols that need to be involved in the required query anyway ).

Oftbl ([ID] is NOT the primary key btw):
ID | Value | End_Date
1 | Yes | 20/03/2011
1 | No | 2/04/2011
2 | No | 20/03/2011
2 | Yes | 2/04/2011
3 | Yes | 20/03/2011
4 | Yes | 2/04/2011
4 | Yes | 2/04/2011
4 | Yes | 20/03/2011
4 | No | 2/04/2011

Mtbl ([ID] IS the primary key on this table):
ID | Name | URL
1 | Fred | http...1
2 | Dave | http...2
3 | Colin | http...3
4 | Frank | http...4

A return dataset would be:
Dave,http..3
Frank,http..4

Because they have Values of "yes" and end_Date is greater than today. ID 4 does'nt get repeated as I only want distinct records.

@Sachin, That's almost it but I need to join data from Mtbl for the Name and URL fields.

Thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-27 : 15:48:07

quote:
Originally posted by PeteLeHoq

Because they have Values of "yes" and end_Date is greater than today. ID 4 does'nt get repeated as I only want distinct records.

@Sachin, That's almost it but I need to join data from Mtbl for the Name and URL fields.

AS per given information the column having 'Yes' does not identify distinct information as it is repeated for ID=4. By the way if its the Typo and ID column is used as primary and foreign keys of the above tables then use inner/left join as required in the from clause data set .. e.g.

SELECT <add required columns of the dataset> From
(SELECT ROW_NUMBER() OVER (PARTITION BY Oftbl.Name ORDER BY Oftbl.Name) AS RowNo, Oftbl.Name, Oftbl.Desc FROM Oftbl
Left Join Mtbl ON Oftbl.ID=Mtbl.ID
WHERE Oftbl.[end_date] >= DATEADD(day,-5,GETDATE()) AND Oftbl.[Value] = 'Yes' )Oftbl
WHERE Oftbl.RowNo=1 and(RowNo between X and Y)

Cheers
MIK

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-27 : 17:21:13

Thanks MIK, but I get an error on this, "The multi-part identifier "Mtbl.Name" could not be bound. I think maybe because the query is for ID on both tables as the primary keys, but they're not.

Mtbl.ID - Is the primary Key
Otbl.ID - Is NOT the primary Key. Another field is but it's not involved in this query.

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-28 : 10:40:54

Anyone any ideas?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 12:19:17

Pete, I looked at this twice, each time I am confused because in your sample data, Dave is ID=2, but in the output it looks like he is ID=3. So, what I am typing here is just based on guesses. If it doesn't work for you the first time, please don't try to debug it or fix it, because I may have completely misunderstood what you are looking for. Also, I did not/could not test this because of my lack of understanding of what is required

select * from
(
   select
      row_number() over (order by Name) as MtBlrowNum,
      m.*
   from
      Mtbl m
      cross apply
      (
         select 
            row_number() over (partition by Id order by End_date desc) as OftblRowNum,
            *
         from
            Oftbl o
         where
            o.Id = m.Id
            and o.Value = 'Yes'
      ) o
   where
      o.OftblRowNum = 1
      and o.[end_date] >= DATEADD(day,-5,GETDATE())
) t
where MtBlrowNum between 1 and 3

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-28 : 12:37:54

Sunita - apologies, that was a typo in the data, does'nt help does it!.

I've tested your script and it's spot on many thanks, it gives me the data required.

Looks like I needed to use cross apply and some other formatting. More self teaching to come.

Peter.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 12:40:49

You don't necessarily need to use cross-apply. I sort of like the sound of "cross-apply", so I tend to use it where it is needed, and where you could do with a sub-query or inner join, often at the expense of performance. If you have performance problems, we can rewrite the query if you verify the logic.

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-04-03 : 16:55:41

sunita, that sql statement is working great.

What would I do to it if I wanted to put in a "where" clause.

I want to say, on the MtBl table, only select records where category = (whatever), amongst everything that the statement does already?

Here's the running statement:

select * from
(
select
row_number() over (order by Name) as MtBlrowNum,
m.*
from
Mtbl m
cross apply
(
select
row_number() over (partition by Id order by End_date desc) as OftblRowNum,
*
from
Oftbl o
where
o.Id = m.Id
and o.Value = 'Yes'
) o
where
o.OftblRowNum = 1
and o.[end_date] >= DATEADD(day,-5,GETDATE())
) t
where MtBlrowNum between 1 and 3

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-03 : 19:04:04

You can add the additional where conditions to the inner where clause (not the innermost), for example


         ...
         where
            o.Id = m.Id
            and o.Value = 'Yes'
      ) o
   where
      o.OftblRowNum = 1
      and o.[end_date] >= DATEADD(day,-5,GETDATE())
      and m.category in ('Books','Magazines')
      ...

You can even have conditions which compare columns from the two tables, for example:


      and m.price < o.price

etc.

One other thing I would recommend is to list out the columns explicitly instead of using the "*". For example,


   ...
   select
      row_number() over (order by Name) as MtBlrowNum,
      m.*
      m.Id,
      m.URL,
      m.Name
   from
      ...

It would not make any difference in performance or behavior right now, but with very few exceptions, it is the recommended practice - for a variety of reasons .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources