DISTINCT和ORDER BY [英] DISTINCT and ORDER BY

问题描述

我有一张产品名称和单独价格的表格。

我想按字母顺序显示不同的产品名称，但是同时显示最便宜的价格。


我当然可以使用GROUP BY作为产品名称，但当我将PRODUCTNAME的订单应用于GROUP BY时，它会列出第一个产品在数据库中而不是最便宜的产品。我不能使用INNER JOIN，因为它来自一个表。


我已经尝试了很多东西来使这个可能很简单的查询工作。


SELECT * FROM PRODUCTS

GROUP BY PRODUCTNAME按PRODUCTNAME排序

Hi,

I have a table with product names and separate prices.
I want to show the distinct product names in alphabetical order, but at the same time show the cheapest price first.

I can of course use GROUP BY for the product names, but when I apply the order by PRODUCTNAME to the GROUP BY it will list the first product in the database and not the cheapest product. I can not use INNER JOIN as it is from one table.

I have tried so many things to get this potentially simple Query to work.

SELECT * FROM PRODUCTS
GROUP BY PRODUCTNAME order by PRODUCTNAME

推荐答案

尝试多件事订购


SELECT * FROM PRODUCTS

GROUP BY PRODUCTNAME按价格订购asc，PRODUCTNAME


ASC正在提升，将价格从最便宜到昂贵，desc是降序，将列出产品字母

Try order by multiple things

SELECT * FROM PRODUCTS
GROUP BY PRODUCTNAME order by Price asc, PRODUCTNAME

The ASC is ascending, that will list price from cheapest to expensive, and the desc is descending which will list product alphabetic

感谢您，我之前尝试过这个。这样做是改变列表，首先显示最便宜的产品而不是产品名称。

Thanks for this and I have previously tried this. What this does is change the list to show the cheapest products first and not the product name first.

您必须在订购时享有某种优先权。


最便宜的产品首先会列出最便宜的产品，无论字母顺序如何（但字母将是次要的订购优先顺序）


如果你想要字母是最优先的那么你只需要按产品名称订购，而不是价格asc。


但你显然不能同时拥有这两种产品。订购优先级只能是一个或另一个。

Well you have to have some kind of priority with the ordering.

Cheapest products first will list cheapest products first regardless of alphabetical order (but alphabetic will be a secondary ordering priority)

If you want alphabetic to be the highest priority then you just need to Order By ProductName, Price asc instead.

But you can''t have both obviously. The ordering priority can only be one or the other.

这篇关于DISTINCT和ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！