0

Given the table below, how can I select only rows that have a unique animal?

animal   age    location
-------------------------
zebra    4      africa        -->  return this row
owl      50     america       -->  return this row
owl      50     u.s.a
panda    9      china         -->  return this row

My understanding is that the following won't work due to location having unique rows:

SELECT DISTINCT animal, age, location FROM table
ecoe
  • 4,994
  • 7
  • 54
  • 72

2 Answers2

2

Short answer - it is impossible, because condition is not strict, how you can determine which row for "owl" should be returned?

Long answer, as soon as you add some conditions, you can select something close to your question, like this:

SELECT animal, MIN(age), MIN(location) FROM table
GROUP BY animal

in this query we're selecting unique animals and minimal values for age and location, note - it doesn't mean that there will be row with such values, like here:

animal1, 2, location1
animal1, 1, location2

this query will select:

animal1, 1, location1
Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
1

Short answer - it is possible with a subquery to a partition table. I find it best to create a View in your MS SQL that defines the partition query and then call that view in your SELECT statement.

The MS SQL View (e.g. vw_First_Animals) would be:

SELECT * FROM (
  SELECT *, rn=row_number() OVER (PARTITION BY [animal] ORDER by [location]) FROM [dbo].[tblAnimals] 
) x where rn = 1;

This makes your XL query,

SELECT * FROM vw_First_Animals

If you reshape the ORDER BY clause to the View, you can change the order that the unique returns are delivered.

I would be remiss without nodding toward this thread that has helped me with PARTITION table subqueries through its simplistic demonstration.

Community
  • 1
  • 1
  • I can't seem to get it to work... does your block of code directly paste into the Excel "Connection Properties" "Command Text" SQL query? I am not working with MS Access - solely Excel. I am connecting to a table in another Excel document (e.g. http://www.wikihow.com/Embed-a-SQL-Query-in-Microsoft-Excel except I'm connecting to another excel document) – ecoe Dec 19 '14 at 00:52
  • @ecoe - My block of code suggests that you create a *View* in your SQL engine that preforms the `SELECT` into a distinct list using a partition table query and then call the *View* from Excel, not the partition table query. I don't think Excel has the capabilities to call the partition table query directly. –  Dec 19 '14 at 23:23
  • interesting, how would I import that View into excel? Could you add a bit to your answer about how Excel can access that View? – ecoe Dec 28 '14 at 19:34