CasJobs: How to make a query that runs through a table an only selects the minimum value if another criteria in the same row repeats in other rows.

I have a query in CasJobs (a site for astronomy searches) that I’m having a bit of trouble with. I want the query to select everything from the table in my FROM block, but some of the mcp_count’s repeat in other rows yet have different angular_sep.

Basically, I want to copy the original table, but only keep the row with the smallest angular separation if the mcp_count repeats in another row.

Here’s my code:

select

mcp_count, specObjID, ra, dec, min(all a.angular_sep)

into

myDB.MegamaserNoRepeatSpecAngularSep

from

myDB..MegamaserSpecAngularSepTest

group by

mcp_count

having

count(mcp_count)>1

order by

mcp_count

When I run this query, I get an error that tell me to put everything that is in my SELECT block into my GROUP BY block as well. When I do this, I still get an error message. Any ideas on how I can make this work?

submitted by /u/Willheim19
[link] [comments]


Go to Source of this post
Author Of this post: /u/Willheim19
Title Of post: CasJobs: How to make a query that runs through a table an only selects the minimum value if another criteria in the same row repeats in other rows.
Author Link: {authorlink}