SQLTeam.com | Weblogs | Forums

Select only one entry, but from another column


#1

Hello everyone,

my name is Nicola, I am as new on this forum as SQL is new to me.
Maybe someone can help me with this problem:

I have this table:

---Project-------Sheet----
-----1------------345-----
-----1------------645-----
-----1------------123-----
-----2------------543-----
-----3------------756----
------3------------987---
Project numbers can be double, sheet numbers are never double.
I need to select only one sheet per project, no matter which one.

So this is what my result should look like:

Sheet
345
543
756

Or like this:

Sheet
645
543
987

I tried the following, but single Project numbers are not found:

	SELECT ProjectNr, max(Sheet)
	FROM table
        GROUP BY ProjectNr

What can I do?

Thanks in advance,

Nicola


#2

You can use row number function

With myProject
As
(
Select row_number() over(partition by project order by sheet) as rnk,  project,  sheet from ProjectNr
) 

Select p.project, p.sheet from my project as p
where p.rnk=1

#3

Project numbers that appear only once should definitely still be listed by

SELECT ProjectNr, max(Sheet)
FROM table
GROUP BY ProjectNr

Something else must be going on with that query.