Hi,

I need to get a unique column but I also need to return multiple rows.

Using the following doesn't work to get the unique last name since it
gets all unique columns.

select unique userid, title, address
from myTable
where etc.........
order by userid

The userid will be returned multiple times because the other columns
cause the row to be unique.

How do I get a unique userid while still returning the other rows.

Thanks!
Dave

test@m.com wrote:
maybe an example to clarify your requirements?

On 09/19/2011 05:38 PM, test@m.com wrote:
It's either multiple rows per userid or one row per userid. How would
you imagine a table where userid occurs 10 times but the other values 34
times? This cannot be done with SQL. You either need two queries (one
for the unique user ids, one for all the rows) or just one which will
then have multiple occurrences of individual userids. I agree to
Gerard: we need more specific requirements.

Kind regards

robert

On Mon, 19 Sep 2011 10:38:54 -0500, test@m.com wrote:

Thanks for the replies.

Lets say a table has the following data.

userid title zipcode
-----------------------------
1 mgr 12345
2 dev 56789
1 mgr 56789

And I wanted to return one row for each unique userid such as...
1 mgr 12345
2 dev 56789


If I use - select unique userid, title, zipcode
it will return all 3 rows because each row is unique

userid title zipcode
-----------------------------
1 mgr 12345
2 dev 56789
1 mgr 56789

I only want unique userids, so it should return. I realize that there
are two different rows for userid = 1 but returning only one would be
OK. The last one would be good.

userid title zipcode
-----------------------------
1 mgr 56789
2 dev 56789

Thanks for any help.
Dave.

On Sep 20, 1:28*pm, t...@m.com wrote:
What does "last one" mean?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...stealing-from/

On 2011-09-20 22:28, test@m.com wrote:
[...]
select userid, title, zipcode
from (
select userid, title, zipcode,
row_number() over (partition by userid) as rn
from myTable
) as T
where rn = 1

if you have a preference for which row that should be returned, you can
add an order by clause:

row_number() over (partition by userid
order by zipcode) as rn

Untested

/Lennart

On 09/21/2011 06:38 AM, Lennart Jonsson wrote:
In that case I'd prefer this as it is so much simpler:

select userid, title, min(zipcode) as zip_min
from myTable
group by userid, title

Kind regards

robert

On 2011-09-21 19:28, Robert Klemme wrote:
[...]
If a user can have several titles it won't work, but that can of course
be solved by adding an aggregate functions on title.

/Lennart

On 21.09.2011 23:11, Lennart Jonsson wrote:
Right, but the OP is not very specific about the data and from the
little he has shown it seems id and title correlate. Even though that
would be a denormalized schema...

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/