Repeated results (how to distinguish them)? 1

[yjoke]

Hi there,

I've been tryin to retrieve the data in a table that has 6 primary fields and some other more regular ones..
I've inserted in it all the possible combinations of the pk fields in the aplication i'm creating, but when i'm returning all the data in the fields i can't avoid to have repeated data for the first PK field..
I've tried to do something like :

select distinct(FirstPKField),* from DataTable

But in SQL 2K it's retrieving all of the combinations..
What I'm looking for, is a way to supress all the repeated FirstPKField Data and return it only once with another field that indicates de date of creation (this one is common to all the records).. All the other fields in the table have to be in the query result also but they are not visible to the end user..
Is this possible??
I've searched in websites for an answer but couldn't get one.. hope here is the right place! ;-)

TIA,

YJoke

 

[bperry]

Does this do it for you?

Select FirstPKField, MAX(DateCreated)
From DataTable
Group ByFirstPKField

--------------------
rgrds, etc
brian perry

 

[nra]

I don't think this is possible. When you say '*' in the select statement, that will fetch all the records in the table. So, the 'distinct' reserved word has no significance.

 

[bperry]

I think I agree with nra.

i.e. for each distinct row of FirstPKField, there could be many possible values for SecondPKField, ThirdPKField, etc. What values would you want returned for those fields......MAX(SecondPKField)? MIN(SecondPKField)? If you don't return some sort of summary values for each subsequent PK field, then FirstPKField cannot be kept distinct.

 

[yjoke]

I'm sorry i've made a mistake in my last thread ..
It isn't a Date type field.. it's a description field, and the idea is to return all the data in the table only showing to the end user the FirstPKField and it's discription without repeating the data that it's shown..

Right now my results are something like this:
Other PK Fields Not Shown
FirstPKField | Description | -- |SecPK |ThirdPK| 4THPK|
---------------------------- -----------------------
AD |Alpha Data | |A |A |A |
AD |Alpha Data | |A |B |A |
AD |Alpha Data | |A |A |B |
AD |Alpha Data | |A |B |B |
AD |Alpha Data | |B |B |A |
AD |Alpha Data | |B |A |B |
AD |Alpha Data | |B |B |B |
BD |Beta Data | |A |A |B |
GD |Gamma Data | |A |B |A |

And i'm loking for something like this:

Other PK Fields Not Shown
FirstPKField | Description | -- |SecPK |ThirdPK| 4THPK|
---------------------------- -----------------------
AD |Alpha Data | |whatever * |
BD |Beta Data | |A |A |B |
GD |Gamma Data | |A |B |A |

* - Doen's matter the returned values here, i will handle this data with some routines i made..

Hope this helps all of u to understand my question..

Thankx

PS - In the preview all worked well with the 'graphical part'.. let's hope this will not be all mixed up..

 

[bperry]

At least for my benefit (if not anyone else) could you explain what you mean by "whatever *".

What I mean is, for the one distinct row for AD, what should be returned for the colums PK2, PK3, and PK4?
A | A | A | ?
A | B | A | ?
A | A | B | ?
A | B | B | ?
etc

Perhaps none of these? perhaps blank? perhaps Null?
In other words, when you return a distinct row for AD, there are a variety of things that could be returned into these other fields. Your query needs to be told what to do.

 

[yjoke]

As i said (you must haven't seen it..) :

"* - Doen's matter the returned values here, i will handle this data with some routines i made.." - regarding the "whatever *" ..

I meen.. any returned value (or none) is acceptable.. in the application i will handle all the data without those values..

 

[bperry]

I see. yess, I guess i did miss that.
I'm thinking three possible options here.

(1) If you don't care what gets returned, then don't return anything. ( I guess that was my original suggestion)
Select FirstPKField, MAX(Description)
From DataTable
Group ByFirstPKField


(2) If you don't care what gets returned, but want some columns preserved, then just return some junk, like this:
Select FirstPKField, MAX(Description),
'SecondPKField' as pk2, 'ThirdPKField' as pk3, 'FourthPKField' as pk4
From DataTable
Group ByFirstPKField

A variation of this would be to return junky numbers instead:
Select FirstPKField, MAX(Description),
200 as pk2, 300 as pk3, 400 as pk4
From DataTable
Group ByFirstPKField


(3) If you want to return real values from those columns, but don't care what they are, then:
Select FirstPKField, MAX(Description),
MAX(SecondPKField), MAX(ThirdPKField), MAX(FourthPKField),
From DataTable
Group ByFirstPKField

It sounds like Option #3 is what you want. Right?

 

[yjoke]

bperry Thank U a LOT !!

Your Third option really did the trick..
I guess i got lost thinking about distinct(Field) function..

You where a real TipMaster.. hehe
Hope to c you arround !!

Cheers,

YJoke

 

[Guest_imported]

FYI:

select distinct(XYZ),* from TABLE_NAME

is the same as saying

select * from TABLE_NAME

you are selecting against rows of data, not columns.