0

Given the below table:
|idAsPrimaryKey|Id - it has a diff name, but it is easier like this|column A|

How can I select in a single sql query, not database server specific, something similar to:


List of results = null
for each different id:
   if there is a row for this id that has for column A the value V1
         ListOfResults add this found row
   else
   if there is a row for this id that has for column A the value V2
         ListOfResults add this found row
   else
        add to ListOfResults the first row found for this id
Roxana
  • 1,569
  • 3
  • 24
  • 41

1 Answers1

1

Quite easy, since you don't seem to know anything about SQL, here's a "teach a man how to fish..." answer.

You have an amount of data and "only" a language how to get data, nothing to really "program". (Of course there are functions and procedures and so on, but those are used in other circumstances or the programmer makes things more complicated than necessary)

Because of this, you have to find a way, how to combine the data, sometimes even with itself, to get what you want. This blog post explains the basics of joins (that's how you combine tables or data from subqueries): A Visual Explanation of SQL Joins (for critics of this post, please read on...)

With this basic knowledge you should now try to create a query, where you join your table to itself two times. To choose the right value for your ListOfResults you then have to use the COALESCE() function. It returns the first of its parameters which isn't NULL.

Here comes the critic for the link I posted above. The Venn diagramms used in the first link don't represent how much data you get back from joining. For this to learn, read this answer here on SO: sql joins as venn diagram

Okay, now you learned, that you might get more data back than you might expect. And here comes another problem in your wording of your question. There's no "first" row in relational databases, you have to exactly describe which row you want, else the data you get back is actually worth nothing. You get random data. A solution for both problems is using GROUP BY and (important!) an appropriate aggregate function.

This should be enough info for you to solve the problem. Feel free to ask more questions if anything is unclear.

Community
  • 1
  • 1
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • thank you - I am going to try to find a solution. However, I need something to work independent from the server type - is coalesce going to work in all the server types? (including possible non-relational databases) – Roxana Mar 07 '14 at 17:59
  • Coalesce() is part of the sql standard if I remember correctly. And even if not, every serious database out there has a way to simulate it. From the top of my head I don't recall a database which hasn't coalesce(). – fancyPants Mar 07 '14 at 18:21