SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL, "DISTINCT", Left Join and Row_Number!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PeteLeHoq
Starting Member

37 Posts

Posted - 03/27/2011 :  13:59:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 03/27/2011 :  14:33:52  Show Profile  Reply with Quote
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.
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 03/27/2011 :  14:59:46  Show Profile  Reply with Quote
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

Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 03/27/2011 :  15:30:40  Show Profile  Reply with Quote
@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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/27/2011 :  15:48:07  Show Profile  Reply with Quote
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
Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 03/27/2011 :  17:21:13  Show Profile  Reply with Quote
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.
Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 03/28/2011 :  10:40:54  Show Profile  Reply with Quote
Anyone any ideas?

Thanks.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 03/28/2011 :  12:19:17  Show Profile  Reply with Quote
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
Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 03/28/2011 :  12:37:54  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 03/28/2011 :  12:40:49  Show Profile  Reply with Quote
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.
Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 04/03/2011 :  16:55:41  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/03/2011 :  19:04:04  Show Profile  Reply with Quote
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 .
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000