June 8, 2017 at 11:53 am
how to get lowest value fom the code column from the query.
By looking at the below data i need only 64 record to show in the results.
I tried to use Min function but still not working.
i have a query where it is joined with 2 to 3 tables.
select ID, Code, EmpNum
from table1 inner join Table2
on
Table1.EmpNum= Table2.EmpNum
and Table1.Code = Table2.Code
where conditons
group by ID,code,EMpNUm
Data shows like
ID Code EmpNum234 64 0800027
234 65 0800027
June 8, 2017 at 2:25 pm
mcfarlandparkway - Thursday, June 8, 2017 11:53 AMhow to get lowest value fom the code column from the query.
By looking at the below data i need only 64 record to show in the results.I tried to use Min function but still not working.
i have a query where it is joined with 2 to 3 tables.
select ID, Code, EmpNum
from table1 inner join Table2
on
Table1.EmpNum= Table2.EmpNum
and Table1.Code = Table2.Code
where conditons
group by ID,code,EMpNUmData shows like
ID Code EmpNum234 64 0800027
234 65 0800027
Store the min value in a variable and then compare on it. Something like:DECLARE @minValue INT
SELECT @minValue = MIN(Code)
FROM table1
INNER JOIN Table2
ON
Table1.EmpNum= Table2.EmpNum
AND Table1.Code = Table2.Code
SELECT ID, Code, EmpNum
FROM table1
INNER JOIN Table2
ON Table1.EmpNum= Table2.EmpNum
AND Table1.Code = Table2.Code
WHERE Table1.Code = @minValue
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 8, 2017 at 2:35 pm
As an alternative, if you really just want the 1 row with the lowest code, then you could use TOP 1...ORDER BY CODE ASC
Cheers!
June 8, 2017 at 2:45 pm
Its showing error -
Parse error at line: 1, column: 28: Incorrect syntax near '='.
June 8, 2017 at 4:08 pm
mcfarlandparkway - Thursday, June 8, 2017 2:45 PMIts showing error -Parse error at line: 1, column: 28: Incorrect syntax near '='.
Jacob's solution is actually a bit cleaner.
Although, I can see no reason why the query I provided would faile at line 1 as line 1 is a DECLARE statement and doesn't contain an = character.
Most of my code was copy-pasted from yours with the exception of adding a new variable and assigning it a value.
Can I get you to post what exactly you wrote that got that incorrect syntax error?
And to confirm this is SQL Server 2012 that you are working with? My code should work on any version of SQL server (as should Jacob's) as they are not doing anything tricky.
I tested my code (with some modifications to work with my tables) and it ran without issue.
Jacob's answer though is a lot nicer than mine. Less memory used, nicer execution plan, less tempdb usage, few reads... overall a much better solution.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply