November 1, 2017 at 2:50 am
Friends,
I have few records like this.
**empNum SlNo CODE
4001 1 SRM
4002 2 SRD
4003 3 SRA
4004 4 SRQ
4000 5 SRQ
4001 6 SRQ
4002 7 SRM
4003 8 SRM
4004 9 SRM
4000 10 SRP
4001 11 SRX
4002 12 SRD
4003 13 SRQ
4004 14 SRA
I want to make some queries whose result will satisfy these criteria.
i want to get distinct employee codes
whose "CODE" value = search criteria value i Will give
and
whose SL.NO is the max of the SL.no value for that particulat SL. no
i.e fetch the record whose SL.No is maximum when grouped by the empnum and whose code is what we specifed
November 1, 2017 at 3:04 am
What does your query look like so far?
ie. What have you tried?
November 1, 2017 at 3:10 am
Hi Paulo
i tried a sub query. ....but it does'nt works
select * from mytable where `SlNo`=(select max(`SlNo`) from mytable group by empNum)
am getting message subquery returns too much record
November 1, 2017 at 3:18 am
Sorry Thom for the confusion...am bad at phrasing my question ..
What i meant was either i will specify ans search for a particular emp code or i will need all empcode , the max value for the (SL.No ) and its realted other field values.
November 1, 2017 at 3:30 am
Ok, well I've created the DDL and DLM for you, but you should really do this yourself.
Now, I've kind of guessed what you mean still, but I've assumed you want the highest SiNo for each Employee.
Note that this answer isn't really best practice, but, if you have a small table, this isn't going to be an issue. If it is much larger, however, then you should separate your query is two parts; one that runs if @code is NULL and returns all results, and one that filters.
CREATE TABLE #Employee
(EmpNum int,
SINo tinyint,
Code char(3));
GO
INSERT INTO #Employee
VALUES
(4001,1,'SRM'),
(4002,2,'SRD'),
(4003,3,'SRA'),
(4004,4,'SRQ'),
(4000,5,'SRQ'),
(4001,6,'SRQ'),
(4002,7,'SRM'),
(4003,8,'SRM'),
(4004,9,'SRM'),
(4000,10,'SRP'),
(4001,11,'SRX'),
(4002,12,'SRD'),
(4003,13,'SRQ'),
(4004,14,'SRA');
GO
SELECT *
FROM #Employee;
GO
--Answer to question
--Assuming you are using a parameter
DECLARE @Code char(3);-- This would contain your sarch value, or be NULL
WITH RN AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY empNum ORDER BY SINo DESC) AS Emp_RN
FROM #Employee)
SELECT EmpNum, SINo, Code
FROM RN
WHERE Emp_RN = 1
AND (Code = @Code OR @Code IS NULL);
GO
DROP TABLE #Employee;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply