| Author |
Topic |
|
Velnias
Yak Posting Veteran
58 Posts |
 Posted - 2008-08-05 : 11:50:29
|
| SELECT * FROM (SELECT row_number() OVER (ORDER BY displayPosition, surname) as resultNum, tblStaff.*, LEFT(surname, 1) AS initial FROM tblStaff INNER JOIN tblStaffToDivision ON tblStaff.id = tblStaffToDivision.staffId INNER JOIN tblDivisions ON tblStaffToDivision.divisionId = tblDivisions.id WHERE " + FilterQuery + ") as numberResults WHERE resultNum BETWEEN " + startRow + " AND " + endRow;Now the problem is im trying to group by tblStaff.Id because im getting duplicates if the same staffId is in multiple tblStaffToDivision |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-05 : 12:52:26
|
| You should be using explicit column lists rather than "*". I guess your WHERE clause is using tblStaffToDivision and tblDivisions columns - Otherwise no need to include them in the FROM clause.Post the sql code resulting from this expression as well sql error you are receiving.Be One with the OptimizerTG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-05 : 12:56:18
|
quote:
Originally posted by Velnias
SELECT * FROM (SELECT row_number() OVER (ORDER BY displayPosition, surname) as resultNum, tblStaff.*, LEFT(surname, 1) AS initial FROM tblStaff INNER JOIN tblStaffToDivision ON tblStaff.id = tblStaffToDivision.staffId INNER JOIN tblDivisions ON tblStaffToDivision.divisionId = tblDivisions.id WHERE " + FilterQuery + ") as numberResults WHERE resultNum BETWEEN " + startRow + " AND " + endRow;Now the problem is im trying to group by tblStaff.Id because im getting duplicates if the same staffId is in multiple tblStaffToDivision
What as per you reuirements should be value of other fields retrieved when you take distinct of tblStaff.Id? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-05 : 13:08:56
|
| As I said, it looks like the OP is only SELECTing columns from tblStaff which just has one row per tblStaff.[ID] That is why I surmised that the OP is only using the other tables for the WHERE clause which we can't see.Velnias,You stated an objective as your "problem". What is preventing you from putting in a GROUP BY clause?Use an explicit column list in your SELECT clause and group by the same columns but don't put the row_number() function in the GROUP By clause.Be One with the OptimizerTG |
|
|
|
Velnias
Yak Posting Veteran
58 Posts |
Posted - 2008-08-05 : 15:03:26
|
| ye the where clause is like where tblDivision.name like '%something%' and tblStaff.statusId = 1the error I get is like , you are trying to use an aggregate function without have all the items in the aggregate function etc.. basically i have more item in my select than my group by...But i want to group by tblStaff.id to remove duplicate staff members i receive in my result set...The other fields in my select query should just return the values of my select statement which works fine but im just receiving duplicates staff id's which is technically correct, but i wish to remove them by using group by.. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-05 : 15:08:34
|
| You will either need to GROUP by the displayed dupe columns or aggregate them. ie: min, max, sum, etc...If you need to select just one of the (entire) rows which are duped then you need to decide on the criteria of which row you want such as latest row based on some date column etc. That is what Visakh was referring to.Be One with the OptimizerTG |
|
|
|
Velnias
Yak Posting Veteran
58 Posts |
Posted - 2008-08-05 : 18:08:31
|
| hmm i cant really aggregate them as I want to return name,location, position etc...So the only way I can do this is to type for example...group by name,location,position,etc... as in type in all of the column names ?Seems very week...Or is there anyway I can use a join with distinct tblStaff.Id or something ? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-05 : 21:05:09
|
| >>...as in type in all of the column names ?what, you only have one finger? And it has a hang-nail??? Type em you lazy bum :)Another possibility is to use all the tables to collect just the distinct list of tblStaff.IDs into a temp table, then select from the temp table JOINed to the tblStaff table to return the results. That would be easier if it was a parameterized SP instead of inline sql.Be One with the OptimizerTG |
|
|
|
|
Trying to get a GROUP BY clause to work