Showing posts with label unique. Show all posts
Showing posts with label unique. Show all posts

Sunday, March 25, 2012

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

DISTINCT on one column only

I have a table I'm running a query on:
SELECT DISTINCT guid, Department FROM table
I only want rows with unique guid's to be returned (there are a couple rows
with identical guids, and I can't fix the real problem of having multiple
guids)
This returns rows with distinct guids and departments obviously. I tried to
modify the query to:
SELECT DISTINCT(guid), Department FROM table
Trying to get it to run the distinct on just the guid column. Still didn't
do it.
What do I need to do to get just the unique guids?
PS The query:
SELECT DISTINCT guid, FROM table
works perfectly.Based on your narrative, you seem to be struggling with a poorly chosen
identifier namely guid. In any case, DISTINCT return distinct rows from a
table, to extract distinct values from a column you will have to use an
aggregate function with a GROUP BY clause like:
SELECT MAX( guid ), department
FROM tbl
GROUP BY department ;
Anith|||SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
David Portas
SQL Server MVP
--|||The unit of work in a SELECT statement is a entire **row**, not a
**column**. The SELECT DISTINCT is for a whole row. You still think
this is "left to right, one field at a time" file system. No wonder
you would have such a poor choice of keys -- you are mimicing a record
number in a file system. You need to stop programming and get a book
on RDBMS basics.
Actually, you need to get rid of that GUID column and get a valid
relational key.
SELECT silly_guid
FROM Foobar
GROUP BY silly_guid
HAVING COUNT(*) = 1;|||- Steve - wrote:
> I have a table I'm running a query on:
> SELECT DISTINCT guid, Department FROM table
> I only want rows with unique guid's to be returned (there are a
> couple rows with identical guids, and I can't fix the real problem of
> having multiple guids)
> This returns rows with distinct guids and departments obviously. I
> tried to modify the query to:
> SELECT DISTINCT(guid), Department FROM table
> Trying to get it to run the distinct on just the guid column. Still
> didn't do it.
> What do I need to do to get just the unique guids?
> PS The query:
> SELECT DISTINCT guid, FROM table
> works perfectly.
You need to figure out which duplicate qualifies as the row you want
returned and then implement the technique that David and Anith describe.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is really close to what I want.
The only problem is that the two rows with the same guid but different
departments, isn't showing at all. I'd like one line of it to show up. (I
don't care which one)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121876475.045441.292920@.g47g2000cwa.googlegroups.com...
> SELECT guid, MIN(department)
> FROM tbl
> GROUP BY guid
> --
> David Portas
> SQL Server MVP
> --
>|||> Actually, you need to get rid of that GUID column and get a valid
> relational key.
I only get to use the data. I have no saying whatsoever in how the data is
managed. It's completley out of my control.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121877566.139416.161400@.g47g2000cwa.googlegroups.com...
> The unit of work in a SELECT statement is a entire **row**, not a
> **column**. The SELECT DISTINCT is for a whole row. You still think
> this is "left to right, one field at a time" file system. No wonder
> you would have such a poor choice of keys -- you are mimicing a record
> number in a file system. You need to stop programming and get a book
> on RDBMS basics.
>
> Actually, you need to get rid of that GUID column and get a valid
> relational key.
> SELECT silly_guid
> FROM Foobar
> GROUP BY silly_guid
> HAVING COUNT(*) = 1;
>|||What you've described isn't what I'd expect. Try the following, which
works for me:
CREATE TABLE tbl (guid UNIQUEIDENTIFIER NOT NULL, department
VARCHAR(10) NOT NULL /* PRIMARY KEY not specified */)
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','A'
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','B'
INSERT INTO tbl SELECT 'C1864626-28CE-4BE3-8171-F4E989DDF114','C'
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
Result:
guid
-- --
9EF7940E-B5A9-4E81-8959-244A7CF31E5F A
C1864626-28CE-4BE3-8171-F4E989DDF114 C
(2 row(s) affected)
Did you do something different? Post some code to reproduce it (like
I've done) if you need more help.
Note that you could also show just the duplicated rows:
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||>> I only get to use the data. I have no saying whatsoever in how the data
is managed. It's completley out of my control. <<
Sorry about that.
I do not drive the train
I cannot ring the bell
but let the damn thing jump the track
and see who catches Hell.

Wednesday, March 21, 2012

Distinct and Unique

Hi
Distinct(in oracle and SQL server) and Unique(in oracle) are they both same
Can we use them interchangeably?
Thanks
JP> Distinct(in oracle and SQL server) and Unique(in oracle) are they both
> same
> Can we use them interchangeably?
I can't speak for UNIQUE in Oracle, so I don't know if it behaves the same
as DISTINCT. But personally, I find very few circumstances where I need to
use DISTINCT...|||>> Distinct(in oracle and SQL server) and Unique(in oracle) are they both sa
me Can we use them interchangeably? <<
No. That is one of MANY Oracle dialect screw-ups to prevent Standard,
portable code. The UNIQUE() constraint is part of the DDL; the
DISTINCT option is part of the DML.|||JP wrote:
> Hi
> Distinct(in oracle and SQL server) and Unique(in oracle) are they both sam
e
> Can we use them interchangeably?
> Thanks
> JP
Instead of Oracle specific
select unique n from(
select 1 n from dual
union all
select 1 n from dual) t
use
select DISTINCT n from(
select 1 n from dual
union all
select 1 n from dual) t
that will give you exactly the same functionality AND it will be
portable|||or for unions use union instead of union all
select 1 n from dual
union -- all take all ouy of you need distinct in a union
select 1 n from dual
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alexander Kuznetsov wrote:
> JP wrote:
> Instead of Oracle specific
> select unique n from(
> select 1 n from dual
> union all
> select 1 n from dual) t
> use
> select DISTINCT n from(
> select 1 n from dual
> union all
> select 1 n from dual) t
> that will give you exactly the same functionality AND it will be
> portable|||I deliberately wrote union all to provide duplicates|||Okay, my bad ;-(
I just wanted to point out that with unions you can use union instead
of union all to get a distinct resultset
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alexander Kuznetsov wrote:
> I deliberately wrote union all to provide duplicates