How to do this? - 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 2000 Forums

SQL Server Development (2000)

How to do this?

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-05-09 : 10:04:42

Here's my query

___________________________________________________________
SELECT distinct(m.Configname+convert(varchar(10), m.REquestdatetime, 101)+m.Username+m.[format]+m.version) as [Distinct Field],
(U.firstname + ' ' + U.lastname) as [Full Name],
M.configname as [Config Name], M.RequestDateTime as [Date Accessed],
M.format as [Format], M.version as [Version]
FROM
smc_new_products.dbo.etechmodelrequests m
join
smc_new_products.dbo.usr_smc u on
convert(varchar(50), u.[user_id])=m.username
where
company_name like '%dow%'
and
M.requestdatetime between '1/1/1999' and '5/9/2003'
order by [Full Name], m.requestdatetime
___________________________________________________________________

I only want to return rows that distinct instances of the [Distinct Field] without using a cursor. Right now this returns results like this:

___________________________________________________________________
C95SDB50-600-F5PWL04/19/200225960sat1.6 Armin Krafft C95SDB50-600-F5PWL 2002-04-19 02:40:37.000 sat 1.6

CDQ2B50-20D05/06/200225960stepAP214 Armin Krafft CDQ2B50-20D 2002-05-06 20:06:01.000 step nAP214

C95SDB63-20009/24/200225960 Armin Krafft C95SDB63-200 2002-09-24 22:25:38.780

C95SDB63-20009/24/200225960stepAP203 Armin Krafft C95SDB63-200 2002-09-24 22:26:40.687 step AP203

C95SDD63-20009/24/200225960stepAP203 Armin Krafft C95SDD63-200 2002-09-24 22:32:42.607 step AP203
___________________________________________________________________

Notice the last two rows are identical according the criteria I used but still show up twice in the results.

Any ideas or help would be much appreciated. Thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-09 : 10:08:56

quote:

C95SDB63-20009/24/200225960stepAP203 Armin Krafft C95SDB63-200 2002-09-24 22:26:40.687 step AP203

C95SDD63-20009/24/200225960stepAP203 Armin Krafft C95SDD63-200 2002-09-24 22:32:42.607 step AP203

They don't look identical to me. You do realize that the DISTINCT keyword SELECTs distinct columns across the entire row, not just the first (***whatever that means

).

Jay White
{0}

label
Posting Yak Master

197 Posts

Posted - 2003-05-09 : 10:45:00

quote:

They don't look identical to me.

That's true....I hadn't caught that. However, later in that same result set I found several that were indentical:

_______________________________________________________________
AW1000-M5BG05/15/200227727 Daniel Vellenoweth AW1000-M5BG 2002-05-15 00:37:07.000

AW1000-M5BG05/15/200227727 Daniel Vellenoweth AW1000-M5BG 2002-05-15 00:38:19.000
_______________________________________________________________

The only thing I don't know about how accurate this is, is that neither record had any "format" or "version" values.

quote:
[b]You do realize that the DISTINCT keyword SELECTs distinct columns across the entire row, not just the first (***whatever that means ).

I'm sorry, I'm definitely not adept at SQL yet so I'm afraid I don't quite understand what you're saying here.

Thanks for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 11:54:53

That Means...

If you say:

SELECT DISTINCT col1, col2, col3

That the composite of all 3 values will be unique, not just col3

So you might get:

"A","B","C"
"A","D","C"

If you want the distinct Values for Col3 you have to do it by itself

SELECT DISTICNT Col3

giving you:

"C"

That's because the rows have a relationship with the other columns in the result set.

Hope that helps

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-10 : 10:34:50

The rows you listed are NOT identical:

AW1000-M5BG05/15/200227727 Daniel Vellenoweth AW1000-M5BG 2002-05-15 00:37:07.000

AW1000-M5BG05/15/200227727 Daniel Vellenoweth AW1000-M5BG 2002-05-15 00:38:19.000

Remember, we are dealing with computers here ... they don't know what you mean if you really are saying "only show rows that are close enough". you need to tell the computer specifically what makes row identical, or what your criteria is.

Don't use DISTINCT. use GROUP BY.

Select field1, field2
from
table
GROUP BY field1, field2

that will return distinct values for field1 and field2. add more fields as needed that must be distinct. in your example, you are NOT counting the date-time field as seomthing that needs to be unique so LEAVE IT OUT of the select clause.

But if you want to return, for each group of records that match up, the MAX() of that field or the MIN() of that field (or in the case of numbers, the SUM()), then you use one of those functions -- hopefully you you can see that logically you must summarize the fields in some way that you do not want to be unique if you still want to select some data from that field.

Never concatenate columns together like you are doing.

What if you have:

Col1, Col2
AA, BB
A, ABB

They will come out the same, but they definitely aren't.

The rule:

if you ONLY want to return the columns that are considered the ones that must be distinct, use

SELECT DISTINCT col1, col2
from table

if you want to return OTHER fields as well, in a summary form, or other expressions as well, then use GROUP BY:

select col1, col2, SUM(col3), MAX(col4), col2 + MIN(Col5), col1 - col2, etc
from table
GROUP BY col1, col2

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources