simple query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

simple query

Author

Topic

akas
Starting Member

42 Posts

Posted - 2009-02-09 : 15:21:23

Hi all,

i have 4fields in given sales table and i m getting results like below: when i am doing query like -

select * from salestable

Name Date Item Price
---------------------------------------------------------
97TH MEDICAL 01/09/2009 05-3290 $100.00
ACE PRESCHOOL 02/04/2009 56-075N $50.00
ADAIR COUNTY 01/28/2009 56-075N $100.00
Adriana Vill 01/23/2009 06-5801 $200.00
Adriana Vill 01/23/2009 06-5801 $200.00
Adriana Vill 01/23/2009 56-075N $100.00
ADVANCED HEART 01/22/2009 05-5226 $500.00
ADVANCED HEART 01/22/2009 08-5066 $500.00
AIMM 12/11/2008 03-5232 $125.00
AIMM 12/11/2008 04-5241 $125.00
AIMM 12/11/2008 04-5242 $100.00
AIMM 12/11/2008 05-3290 $50.00
AIMM 12/11/2008 06-5834 $125.00
AIMM 02/05/2009 05-3290 $50.00
Alice Koller 01/26/2009 56-075N $100.00
Amanda Cantrell 01/21/2009 06-5235 $200.00
Amanda Moore 12/03/2008 56-075N $60.00
AMERICAN DIET 01/23/2009 00-4084 $100.00
Andrea Abbati 01/27/2009 01-3525 $100.00
Andrea Abbati 01/27/2009 01-3667 $100.00

now, i want results like below means if Name is individual then i don't want to display in output, but if Name is more than one then only i want to display that row in output like below. can anyone help me to solve this query. thanks.

Output will be like-

Name Date Item Price
------------------------------------------------------------
Adriana Vill 01/23/2009 06-5801 $200.00
Adriana Vill 01/23/2009 06-5801 $200.00
Adriana Vill 01/23/2009 56-075N $100.00
ADVANCED HEART 01/22/2009 05-5226 $500.00
ADVANCED HEART 01/22/2009 08-5066 $500.00
AIMM 12/11/2008 03-5232 $125.00
AIMM 12/11/2008 04-5241 $125.00
AIMM 12/11/2008 04-5242 $100.00
AIMM 12/11/2008 05-3290 $50.00
AIMM 12/11/2008 06-5834 $125.00
AIMM 02/05/2009 05-3290 $50.00
Andrea Abbati 01/27/2009 01-3525 $100.00
Andrea Abbati 01/27/2009 01-3667 $100.00

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-09 : 15:26:00

[code]Select [Name],[Date],Item,Price
from
(Select Row_Number() over (Partition by [Name] order by [Name])as Seq
* from table)Z
Where Z.seq>1[/code]

akas
Starting Member

42 Posts

Posted - 2009-02-09 : 15:31:46

hi,

thanks for replying...i m getting an error - incorrect syntax near *

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-09 : 15:33:13

Put your tablename.

Select [Name],[Date],Item,Price
from
(Select Row_Number() over (Partition by [Name] order by [Name])as Seq
,* from table)Z
Where Z.seq>1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 15:33:43

Put a comma before the asterisk.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 23:16:38

[code]declare @test table
(
[Name] varchar(100),
Date datetime,
Item varchar(10),
Price money
)
insert into @test
select '97TH MEDICAL', '01/09/2009', '05-3290', '$100.00' union all
select 'ACE PRESCHOOL','02/04/2009', '56-075N', '$50.00' union all
select 'ADAIR COUNTY', '01/28/2009', '56-075N', '$100.00' union all
select 'Adriana Vill', '01/23/2009', '06-5801', '$200.00' union all
select 'Adriana Vill', '01/23/2009', '06-5801', '$200.00' union all
select 'Adriana Vill', '01/23/2009', '56-075N', '$100.00' union all
select 'ADVANCED HEART', '01/22/2009', '05-5226', '$500.00' union all
select 'ADVANCED HEART', '01/22/2009', '08-5066', '$500.00' union all
select 'AIMM', '12/11/2008', '03-5232', '$125.00' union all
select 'AIMM', '12/11/2008', '04-5241', '$125.00' union all
select 'AIMM', '12/11/2008', '04-5242', '$100.00' union all
select 'AIMM', '12/11/2008', '05-3290', '$50.00' union all
select 'AIMM', '12/11/2008', '06-5834', '$125.00' union all
select 'AIMM', '02/05/2009', '05-3290', '$50.00' union all
select 'Alice Koller', '01/26/2009', '56-075N', '$100.00' union all
select 'Amanda Cantrell', '01/21/2009', '06-5235', '$200.00' union all
select 'Amanda Moore', '12/03/2008', '56-075N', '$60.00' union all
select 'AMERICAN DIET', '01/23/2009', '00-4084', '$100.00' union all
select 'Andrea Abbati', '01/27/2009', '01-3525', '$100.00' union all
select 'Andrea Abbati', '01/27/2009', '01-3667', '$100.00'

select [Name] ,Date,Item,Price
from
(select count(*) over (partition by [Name]) as occurance,*
from @test
)t
where occurance >1
output
---------------------------------------------
Name Date Item Price
Adriana Vill 2009-01-23 00:00:00.000 06-5801 200.00
Adriana Vill 2009-01-23 00:00:00.000 06-5801 200.00
Adriana Vill 2009-01-23 00:00:00.000 56-075N 100.00
ADVANCED HEART 2009-01-22 00:00:00.000 05-5226 500.00
ADVANCED HEART 2009-01-22 00:00:00.000 08-5066 500.00
AIMM 2008-12-11 00:00:00.000 03-5232 125.00
AIMM 2008-12-11 00:00:00.000 04-5241 125.00
AIMM 2008-12-11 00:00:00.000 04-5242 100.00
AIMM 2008-12-11 00:00:00.000 05-3290 50.00
AIMM 2008-12-11 00:00:00.000 06-5834 125.00
AIMM 2009-02-05 00:00:00.000 05-3290 50.00
Andrea Abbati 2009-01-27 00:00:00.000 01-3525 100.00
Andrea Abbati 2009-01-27 00:00:00.000 01-3667 100.00[/code]

akas
Starting Member

42 Posts

Posted - 2009-02-10 : 10:07:23

Thanks to all for replying.

Yes, i got the correct results.

thanks to Sodeep, Peso and Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:09:09

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources