quote:
Originally posted by Lumbago
I don't mean to be rude or offensive (really) but I really think that this is a matter of coding technique and not a matter of what MySQL/SQL Server can and cannot do.
Post your MySQL query and I'm 100% positive that it can be solved equally or better in T-SQL.
- Lumbago
My blog-> http://thefirstsql.com
@Lumbadgo None taken, I'm totally confused because the MySQL way of build the query just doesn't work.. So my sql query is pretty weak since I'm used to MySQL. I can't really post the MySQL for it since it's MSSQL so I write roughly what I would normally do.
@Jim that doesn't seem to work or rather this is where I'm confused because mysql just does it but mssql is crying about "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" on every other field I'm trying to select.
I don't want to aggregate or group my results on anything else I just (empID) then I want to take the top record based on the order.
So
select Distinct
CAST (Person.Details."Person Number" AS varchar) as empID,
Person.Details."Title" as empTitle,
Person.Details."First Name" as empFirstName,
Person.Details."Known As" as empKnownAs,
Person.Details."Surname" as empSurname,
Person.Details."Initials" as empInitails,
Person.Details."E-Mail" as empEmail,
Person.Details."Work Phone Number" as empPhoneNum,
Person.Details."Work Extension Number" as empExtNum,
Person."Details Custom"."Room Number" as empRoomNum,
Person."Details Custom"."Line Manager" as empIsLineManager,
Person."Details Custom"."SMT or CMT" as empSMTorCMT,
Person."Details Custom"."Departmental Administrator" as empDepartmentalAdmin,
Employee.Person."Continuous Service Date" as empStartDate,
Employee.Person."Fire Officer" as empFireOfficer,
Employee.Person."First Aider" as empFirstAider,
Employee.Person."Health and Safety Officer" as empHealthOfficer,
Organisation."Post Details"."Post Name" as empPostName,
Organisation."Post Details"."Manager Post Number" as empLineMangerPostID,
Organisation.Locations."Location Name" as empSite,
orgDetails."Unit Name" as empDepartment,
Employee."Career History"."FTE" as empFTE,
carHistExt."End Date" as empExpiryDate,
CAST (manDet."Person Number" AS varchar) as empLineManagerEmpID,
Employee."Appointment History"."Appointment Number"
FROM Person.Details
INNER JOIN Person."Details Custom"
ON Person.Details."Person Number" = Person."Details Custom"."Person Number"
JOIN Employee.Person
ON Person.Details."Person Number" = Employee.Person."Person Number"
JOIN Employee."Appointment History"
ON Employee."Appointment History"."Person Number" = Person.Details."Person Number"
JOIN Employee."Career History"
ON Employee."Career History"."Appointment Number" = Employee."Appointment History"."Appointment Number"
JOIN Organisation."Post Details"
ON Organisation."Post Details"."Post Number" = Employee."Career History"."Post Number"
JOIN Pattern.Details as patDetails
ON Employee."Career History"."Pattern Number" = patDetails."Pattern Number"
JOIN Organisation.Locations
ON Employee."Career History"."Location Number" = Organisation.Locations."Location Number"
JOIN Organisation.Details as orgDetails
ON Organisation."Post Details"."Parent Unit Number" = orgDetails."Unit Number"
JOIN Employee."Appointment History Extra Detail"
ON Employee."Appointment History Extra Detail"."Appointment Number" = Employee."Appointment History"."Appointment Number"
JOIN Employee."Career History Extra Detail"
ON Employee."Career History Extra Detail"."Post ID" = Employee."Appointment History Extra Detail"."Current Post ID"
JOIN Employee."Appointment History Extra Detail" as test
ON Employee."Career History Extra Detail"."Post Number" = Organisation."Post Details"."Post Number"
/* manager information */
JOIN Employee."Career History" as manCar
ON manCar."Post Number" = Organisation."Post Details"."Manager Post Number"
JOIN Employee."Appointment History" as manApp
ON manCar."Appointment Number" = manApp."Appointment Number"
JOIN Employee."Appointment History Extra Detail" as AppHistExt
ON AppHistExt."Appointment Number" = manApp."Appointment Number"
JOIN Employee."Career History Extra Detail" as carHistExt
ON carHistExt."Post ID" = AppHistExt."Current Post ID" AND carHistExt."End Date" IS NULL
JOIN Employee."Career History" as manCar2
ON carHistExt."Career Number" = manCar2."Career Number"
JOIN Employee."Appointment History" as manApp2
ON manApp2."Appointment Number" = manCar2."Appointment Number"
JOIN Employee.Person as manDet
ON manApp2."Person Number" = manDet."Person Number"
WHERE Organisation."Post Details"."Expiry Date" > GetDate()
OR Organisation."Post Details"."Expiry Date" is NULL
AND empID = '13714'
order by empFTE desc
This is my current query. I've add on the AND empID = to a record that has more then 1 post.
This results in this;
13714 Mr Darren Darren XXXXXXX DL DXXXXXXX@XXXXXXX NULL NULL NULL NULL NULL 2007-11-16 00:00:00.000 0 0 0 Associate Lecturer 3926 Road Academy of Education, Social Science, Health and Creative Industries 0.60386473 NULL 16011 15322
13714 Mr Darren Darren XXXXXXX DL DXXXXXXX@XXXXXXX NULL NULL NULL NULL NULL 2007-11-16 00:00:00.000 0 0 0 Part-time Lecturer 3492 Road Academy of Public Services and Business 0.09178744 NULL 15257 18338
Which is fine, but what I really need is to add FTE together for the records and then take the rest of the information from the record with the hights FTE. So that the view only contains a single record per person. |
select Distinct not working like mysql.
Posted - 05/24/2012 : 06:48:14
