Getting Unique values with GROUP BY

[jamesm6162]

Hi

I'm trying to create a view containing 3 fields, all from the same table.

Here's what I have:

SELECT MAX(dateField), field2, field3
FROM table1
GROUP BY field2, field3

Basically I want the highest dateField for each field2, and then just add the corresponding field3 next to it. But this query doesnt give me unique values for field2

If I leave the field3 out of the query as follows:

SELECT MAX(dateField), field2
FROM table1
GROUP BY field2

then I get unique field2 values, but i dont have the corresponding field3 value.

How should I rewrite this query to achieve what i'm trying to?

Thanks

[ck9663]

Hi

I'm trying to create a view containing 3 fields, all from the same table.

Here's what I have:

SELECT MAX(dateField), field2, field3
FROM table1
GROUP BY field2, field3

Basically I want the highest dateField for each field2, and then just add the corresponding field3 next to it. But this query doesnt give me unique values for field2

If I leave the field3 out of the query as follows:

SELECT MAX(dateField), field2
FROM table1
GROUP BY field2

then I get unique field2 values, but i dont have the corresponding field3 value.

How should I rewrite this query to achieve what i'm trying to?

Thanks

will this work:

select a.maxdatefield, table1.field2, table1.field3 from
(SELECT MAX(dateField) as maxdatefield, field2
FROM table1
GROUP BY field2) a
inner join table1 on a.field2 = table1.field2 

-- ck

[jamesm6162]

will this work:

select a.maxdatefield, table1.field2, table1.field3 from
(SELECT MAX(dateField) as maxdatefield, field2
FROM table1
GROUP BY field2) a
inner join table1 on a.field2 = table1.field2 

-- ck

No it doesn't work.
I get way too many results.

[ck9663]

No it doesn't work.
I get way too many results.

would you mind posting the name of these fields?

-- CK

[jamesm6162]

would you mind posting the name of these fields?

-- CK

Yea sure.

They are:
period (datetime), personnel_number (nvarchar), ppaygrade(nvarchar)

The table serves as records of updates. I want to retrieve the latest (period) update for each personnel_number, with the updated ppaygrade value for that period

[ck9663]

Yea sure.

They are:
period (datetime), personnel_number (nvarchar), ppaygrade(nvarchar)

The table serves as records of updates. I want to retrieve the latest (period) update for each personnel_number, with the updated ppaygrade value for that period

requirement: personnel_number + period should be unique. otherwise, which one would you choose?

try:

select a.personal_number, a.period, b.ppaygrade
(select personnel_number, max(period) as period from mytable group by personnel_number) A inner join
mytable B on A.personnel_number = B.personnel_number and A.period = B.Period
 

-- CK

[jamesm6162]

requirement: personnel_number + period should be unique. otherwise, which one would you choose?

try:

select a.personal_number, a.period, b.ppaygrade
(select personnel_number, max(period) as period from mytable group by personnel_number) A inner join
mytable B on A.personnel_number = B.personnel_number and A.period = B.Period
 

-- CK

Okay I see what you mean.

I tried your way above and got 6888 records as opposed to the 6420 unique personnel_numbers in the table.

Just as a test, if i do the following:

Select period, personnel_number FROM myTable 
GROUP BY period, personnel_number

And I get fewer results than when i simply say:

Select period, personnel_number FROM myTable 

then it means I have duplicates for some of the period/personnel_number combinations, am I right?

[ck9663]

Okay I see what you mean.

I tried your way above and got 6888 records as opposed to the 6420 unique personnel_numbers in the table.

Just as a test, if i do the following:

Select period, personnel_number FROM myTable 
GROUP BY period, personnel_number

And I get fewer results than when i simply say:

Select period, personnel_number FROM myTable 

then it means I have duplicates for some of the period/personnel_number combinations, am I right?

yep. that means you have duplicates...

this:

Select period, personnel_number, count(*) FROM myTable 
GROUP BY period, personnel_number having count(*) > 1

will show you what are those duplicates and how many are there ...

-- ck

[jamesm6162]

yep. that means you have duplicates...

this:

Select period, personnel_number, count(*) FROM myTable 
GROUP BY period, personnel_number having count(*) > 1

will show you what are those duplicates and how many are there ...

-- ck

Ok thanks alot for the help.

Im off trying to create the compound query to eliminate these duplicates

cya