April 20, 2011 at 1:05 pm
FIRSTNAME LASTNAME CHECK DATECHECK NO AMOUNT
Mickey Mouse4/20/201110001 $ 1,500.00
Minnie Mouse4/19/201110000 $ 2,500.00
Mickey Mouse4/19/201110000 $ 2,500.00
Minnie Mouse4/20/201110001 $ 1,500.00
I want to write a query that only display one person per check no.
For instance: Mickey Mouse will display with only check no. 10001
April 20, 2011 at 1:12 pm
Would you show the last check or the first check; By check date or by number?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 20, 2011 at 1:32 pm
It will show by the check number
April 20, 2011 at 1:38 pm
The answer is SELECT TOP 1 - depending on the first or last check option, the ORDER BY will be ASC or DESC.
April 20, 2011 at 1:38 pm
You could use Min() or Max() on the name, and group by the rest of it. Won't control which one you show, but will only show one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2011 at 1:57 pm
Assuming you what to return the last check for every person then you could do something like below.
declare @temp table (fristname varchar(20),lastname varchar(20),Check_date
datetime,Checkno int,Amount decimal(10,2))
insert into @temp (fristname,lastname,Check_date,Checkno,Amount)
values ('Mickey', 'Mouse', '4/20/2011', 10001, 1500.00)
insert into @temp (fristname,lastname,Check_date,Checkno,Amount)
values('Minnie', 'Mouse', '4/19/2011', 10000, 2500.00)
insert into @temp (fristname,lastname,Check_date,Checkno,Amount)
values('Mickey', 'Mouse', '4/19/2011', 10000, 2500.00)
insert into @temp (fristname,lastname,Check_date,Checkno,Amount)
values('Minnie', 'Mouse', '4/20/2011', 10001, 1500.00)
select t.fristname,t.lastname,t.Check_date,t.Checkno,t.Amount
from
(
select fristname,lastname,Check_date,Checkno,Amount,
ROW_NUMBER() OVER(PARTITION BY fristname,lastname ORDER BY Checkno DESC) as rownum
from @temp
) t
where rownum=1
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 20, 2011 at 2:23 pm
Yes, there are more ways how to skin this one.
😉
April 20, 2011 at 2:51 pm
Just to be silly I tweaked Dan's a little bit.
select t.fristname,t.lastname,t.Check_date,t.Checkno,t.Amount
from
(
select fristname,lastname,Check_date,Checkno,Amount,
ROW_NUMBER() OVER(PARTITION BY checkno ORDER BY newid() DESC) as rownum
from @temp
) t
where rownum=1
This way you will get a random name for each check each time you run it. It did change up the partition by to Checkno instead of the name so that it will get 1 record for each check instead 1 record for each name. 😀
_______________________________________________________________
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/
May 15, 2011 at 6:27 pm
kdford10 (4/20/2011)
FIRSTNAME LASTNAME CHECK DATECHECK NO AMOUNTMickey Mouse4/20/201110001 $ 1,500.00
Minnie Mouse4/19/201110000 $ 2,500.00
Mickey Mouse4/19/201110000 $ 2,500.00
Minnie Mouse4/20/201110001 $ 1,500.00
I want to write a query that only display one person per check no.
For instance: Mickey Mouse will display with only check no. 10001
I have to ask...
1. Why is the other name OK to ignore?
2. What system is it that allows for such duplication of data?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply