Prev: Updating using groupby
Next: SQL insert query help needed
From: jodleren on 9 Jun 2010 11:52
Hi

I have this query, which I need to make work in MS SQL Server

select job.[CAM File],FIRST([material]), FIRST(job.[description]),
FIRST(job.[programmer]) from job where job.[CAM file] <> ''
group by job.[cam file] order by 3

The "first" is my problem.

I can translate most of into

select job.[CAM File] from job
where job.[CAM file] <> '' group by job.[cam file])

Now I need to add the first row - for each cam file - how?
Next, I need to read something like this:

select top 1 job.[CAM File],
job.[material], job.[description], job.[programmer]
from job
where job.[CAM file] = job_from_above.[CAM file] <- note this one

Just how do I do that?

WBR
Sonnich
From: Plamen Ratchev on 9 Jun 2010 14:59
There is no FIRST equivalent in SQL Server (and tables have no order
of rows, so there is no first row concept). The following will be
close. You can change the ORDER BY clause in the ranking function to
order by a primary key or date column (if there is such and it can be
used to define some order of rows).

SELECT [CAM file], material, description, programmer
FROM (
SELECT [CAM file], material, description, programmer,
ROW_NUMBER() OVER(PARTITION BY [CAM file] ORDER BY (SELECT
NULL)) AS rk
FROM job
WHERE [CAM file] <> '')
WHERE rk = 1
ORDER BY description;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Updating using groupby
Next: SQL insert query help needed