only get the non distinct records

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

only get the non distinct records

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 10:52:46

I am trying to write a query that will only return the non distinct records

what i have is a bunch of line items that have an order id and a rate

there may be 10 line items with the same orderid, I need to get the orderid and rates for the orderids that have more than one rate

how would i do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-01 : 10:53:29

[code]
select t.*
from yourtable t
inner join
(
select order_id
from yourtable
group by order_id
having count(*) > 1
) d on t.order_id = d.order_id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-01 : 10:58:03

[code]SELECT OrderID,
Rate
FROM (
SELECT OrderID,
Rate,
COUNT(*) OVER (PARTITION BY OrderID) AS Items
FROM Table1
) AS d
WHERE Items > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 11:07:04

thanks for the fast reply

Peso -- i get over construct is not supported

khtan -- yours doesnt address the distinct rates

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-01 : 11:22:30

Peter's solution is for SQL Server 2005 only.

try this

select t.*
from yourtable t
inner join 
(
    select order_id
    from   yourtable
    group by order_id
    having count(*) = 1
) d on t.order_id = d.order_id

KH
[spoiler]Time is always against us[/spoiler]

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 11:26:43

adding distinct got it working thanks

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 11:28:51

"thats the same query that you posted before"

It looks to me that khtan very carefully put the part that is different in red for you.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 11:30:35

But if I have understood your question I think you need:


select t.*
from yourtable t
inner join 
(
    select DISTINCT order_id
    from   yourtable
    group by order_id, rate
    having count(*) > 1
) d on t.order_id = d.order_id

Kristen

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 11:39:36

that returns everything -- its not getting just the records with multiple rates

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-01 : 11:43:14

post your table DDL, sample data and result that you want

KH
[spoiler]Time is always against us[/spoiler]

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 11:47:55

sample data

orderid RATE
3DF4-966312330 1
3DF4-966312330 1
3DF4-966312330 1
3E3D-966312331 1
3E3D-966312331 1
3E3D-966312331 2

returns

3E3D-966312331 1
3E3D-966312331 2

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 12:09:40

Why would

3E3D-966312331 2

show, there's only one of those and you said " for the orderids that have more than one rate"

Similarly, why doesn't

3DF4-966312330 1

show?

Kristen

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-01 : 12:25:30

declare @tbl table (col1 varchar(25),col2 int)

INSERT INTO @tbl
select '3DF4-966312330', 1 union all
select '3DF4-966312330', 1 union all
select '3DF4-966312330', 1 union all
select '3E3D-966312331', 1 union all
select '3E3D-966312331', 1 union all
select '3E3D-966312331', 2

select distinct b.*
from
@tbl a
inner join
@tbl b
on
a.col1 = b.col1
and a.col2 <> b.col2

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 12:41:41

Why would
more than one rate in that 1 and 2 are different so their are two rates on that one.

the frist doesnt show because there is only one so i dont need it

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-01 : 13:20:56

jimf what you did got it thanks

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 13:24:32

OK, NOW I understand what your requirements are at last.


select DISTINCT t.*
from yourtable t
inner join 
(
    select order_id
    from   yourtable 
    group by order_id
    having count(DISTINCT rate) > 1
) d on t.order_id = d.order_id

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources