Prev: Procedure to insert into multiple tables
Next: Remove word from end of string
From: Mufasa on 22 Jul 2008 12:56
I've got a list of customers, customer names, ... from another db (I didn't
design it).

I want to get a list of all of the customers plus the first customer name.
So if customer 1 appears twice (customer name = 'Fred' and 'John') I want
the first one it finds.

Is there an easy way to do this without spinning through all of the records.
I'm using the results of the query as a select statement as input to an
insert statement.

TIA - Jeff.


From: Aaron Bertrand [SQL Server MVP] on 22 Jul 2008 13:24
SQL Server doesn't understand the concept of "first" since a table is an
unordered set of rows. Do you have some other column that can be used to
break this tie? e.g. is there a created_date column or something? If so,
you could say:


SELECT
customer_id,
customer_name
FROM
customers c
INNER JOIN
(
SELECT customer_id,
created_date = MIN(created_date) -- or MAX?
FROM customers
GROUP BY customer_id
) s
ON c.customer_id = s.customer_id
AND c.created_date = s.created_date;


I suppose this could still yield ties of you have multiple rows for the same
customer with the same created_date. Hard to suggest anything further
without more details.




"Mufasa" <jb(a)nowhere.com> wrote in message
news:e%23zGtvB7IHA.4652(a)TK2MSFTNGP05.phx.gbl...
> I've got a list of customers, customer names, ... from another db (I
> didn't design it).
>
> I want to get a list of all of the customers plus the first customer name.
> So if customer 1 appears twice (customer name = 'Fred' and 'John') I want
> the first one it finds.
>
> Is there an easy way to do this without spinning through all of the
> records. I'm using the results of the query as a select statement as input
> to an insert statement.
>
> TIA - Jeff.
>
>


 | 
Pages: 1
Prev: Procedure to insert into multiple tables
Next: Remove word from end of string