SQL 选择行,其中 id=max(id)

发布于 12-10 11:12 字数 1168 浏览 0 评论 0原文

我有一个表,其中包含具有相同标题的产品的多个实例的产品信息,通过不同的颜色及其 ID 进行标识。我需要输出 id = 按标题分组的最大 id 的整行,但我似乎无法做到这一点。这是一个非常简化的表格和一些示例数据:

id   title   colour   description

1    rico    red      blah1
2    rico    blue     blah2
3    rico    yellow   blah3
4    katia   black    blah4
5    katia   white    blah5

在这个示例中,我的代码中,当我想要 3 个 rico 黄 blah3 时,我得到 1 个 rico 红 blah1。

这是我正在使用的代码:

SELECT pd_id, pd_title, pd_description, pd_colour,
       pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
WHERE 
  cat_sub_id_1 = '".$cat_sub_id."' 
  AND cat_parent_id='".$cat_parent_id."' 
GROUP BY pd_title 
HAVING MAX(pd_id) 
ORDER BY pd_id DESC

更新:谢谢大家,

我使用 alinoz 的答案得出了以下有效的代码:)

SELECT
    pd_id,pd_title,pd_description,pd_colour,pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
HERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
AND pd_id IN (
    SELECT max(pd_id)
    FROM product
    WHERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
    GROUP BY pd_title
    )
GROUP BY pd_title
ORDER BY pd_id DESC

I have a table which holds product information with multiple instances of a product with the same title, identified by different colours and their ids. I need to output the entire row where the id = the maximum id grouped by the title, but I can't seem to get it to do this. Here is a very simplified table and some example data:

id   title   colour   description

1    rico    red      blah1
2    rico    blue     blah2
3    rico    yellow   blah3
4    katia   black    blah4
5    katia   white    blah5

In this example with my code, I get 1 rico red blah1 when I want 3 rico yellow blah3.

Here is the code I am using:

SELECT pd_id, pd_title, pd_description, pd_colour,
       pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
WHERE 
  cat_sub_id_1 = '".$cat_sub_id."' 
  AND cat_parent_id='".$cat_parent_id."' 
GROUP BY pd_title 
HAVING MAX(pd_id) 
ORDER BY pd_id DESC

UPDATE: Thanks guys,

I used alinoz's answer to come up with the following code which works :)

SELECT
    pd_id,pd_title,pd_description,pd_colour,pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
HERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
AND pd_id IN (
    SELECT max(pd_id)
    FROM product
    WHERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
    GROUP BY pd_title
    )
GROUP BY pd_title
ORDER BY pd_id DESC

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

无所的.畏惧2024-12-17 11:12:10

啊啊啊,古老的 ...

select *
from YourTable yt
inner join(
    select title, max(id) id
    from YourTable
    group by title
) ss on yt.id = ss.id and yt.title = ss.title

当然,您应该相应地根据您的需要进行调整。

另外,我认为这是“必读”:SQL Select only rows with Max Value on a Column

Aaahhh, the good old ...

select *
from YourTable yt
inner join(
    select title, max(id) id
    from YourTable
    group by title
) ss on yt.id = ss.id and yt.title = ss.title

Of course, you should adapt this to your needs accordingly.

Also, I think this is a "must read": SQL Select only rows with Max Value on a Column

ゞ记忆︶ㄣ2024-12-17 11:12:10

我认为这可能有效(未经测试):

SELECT * FROM that_table WHERE id IN (
SELECT MAX(id) FROM that_table GROUP BY title
) /* AND this = that */

I think this might work (not tested):

SELECT * FROM that_table WHERE id IN (
SELECT MAX(id) FROM that_table GROUP BY title
) /* AND this = that */
墨落成白2024-12-17 11:12:10

仅针对任意(在本例中为“id”)最大值的一行

SELECT * FROM `your_table` order by id desc limit 1

simply for one row by any (in this case 'id') max value

SELECT * FROM `your_table` order by id desc limit 1
还如梦归2024-12-17 11:12:10

尝试在 where 中添加一个子句 pd_id = (select max(pd_id) from ... )

SELECT pd_id, pd_title, pd_description, pd_colour,
       pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
WHERE 
  cat_sub_id_1 = '".$cat_sub_id."' 
  AND cat_parent_id='".$cat_parent_id."' 
  AND pd_id = (select max(pd_id) from product)
GROUP BY pd_title

此查询不是最佳的,但它可以完成这项工作。

try to add one more clause to your where with pd_id = (select max(pd_id) from ... )

SELECT pd_id, pd_title, pd_description, pd_colour,
       pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product 
WHERE 
  cat_sub_id_1 = '".$cat_sub_id."' 
  AND cat_parent_id='".$cat_parent_id."' 
  AND pd_id = (select max(pd_id) from product)
GROUP BY pd_title

This query is not optimal but it would do the job.

锦欢2024-12-17 11:12:10

除了 Adrian 和 Salman 的方法之外,还有一种方法,当存在平局、两行或更多行具有相同(最大)id 时,会给出不同的结果。它只会显示每个标题一行,而另一个查询将显示所有行(在您的情况下,这可能与您按 id 排序时无关,我认为这是主键):

SELECT 
    t.* 
FROM 
    TableX AS t
  JOIN
    ( SELECT DISTINCT
          title                        --- what you want to Group By
      FROM TableX 
    ) AS dt
    ON t.PK =                          --- the Primary Key of the table
       ( SELECT tt.PK
         FROM TableX AS tt
         WHERE tt.title = dt.title
         ORDER BY id ASC               --- (or DESC) what you want to Order By
         LIMIT 1
       )

Besides Adrian's and Salman's approach, there's alo this one which gives different results when there are ties, two or more rows with same (maximum) id. It will show only one row per title while the other query will show all of them (in your case that is probably irrelevant as you order by id, which I suppose is the Primary Key):

SELECT 
    t.* 
FROM 
    TableX AS t
  JOIN
    ( SELECT DISTINCT
          title                        --- what you want to Group By
      FROM TableX 
    ) AS dt
    ON t.PK =                          --- the Primary Key of the table
       ( SELECT tt.PK
         FROM TableX AS tt
         WHERE tt.title = dt.title
         ORDER BY id ASC               --- (or DESC) what you want to Order By
         LIMIT 1
       )
夏末的微笑2024-12-17 11:12:10

由于您已经订购,因此可以在 where 条件下使用行号=1。请参阅此链接。因为你的数据库不知道。我无法给出具体的查询,但会指导您方向

或尝试 sql top 其中将产生相同的结果,因为使用了 order by

since you are already ordering, you can use row number =1 in where condition. see this link. and since your db is not known. I cant give a specific query but direct you in the direction

or try sql top which will yield the same result, as order by is used

旧伤还要旧人安2024-12-17 11:12:10

我认为,没有必要使用子选择:

select 
SUBSTRING_INDEX(group_concat(testtab.ActionID order by testtab.ActionID DESC SEPARATOR '|'), '|', 1)  as ActionIDs,
SUBSTRING_INDEX(group_concat(testtab.DiscountedPrice order by testtab.ActionID DESC SEPARATOR '|'), '|', 1)  as DiscountedPrices
testtab.*
from testtab 
where 
testtab.StartDate <= 20160120 
and testtab.EndDate > 20160120
and testtab.VariantID = 302304364
and testtab.DeleteStatus = 0
group by testtab.VariantID
order by testtab.VariantID, testtab.ActionID;

它只是我测试中的一个快速示例。只需在 substring_indexes 中以相同的方式进行排序并使用良好的分隔符即可。

玩得开心

I think, its not necessary to use subselects:

select 
SUBSTRING_INDEX(group_concat(testtab.ActionID order by testtab.ActionID DESC SEPARATOR '|'), '|', 1)  as ActionIDs,
SUBSTRING_INDEX(group_concat(testtab.DiscountedPrice order by testtab.ActionID DESC SEPARATOR '|'), '|', 1)  as DiscountedPrices
testtab.*
from testtab 
where 
testtab.StartDate <= 20160120 
and testtab.EndDate > 20160120
and testtab.VariantID = 302304364
and testtab.DeleteStatus = 0
group by testtab.VariantID
order by testtab.VariantID, testtab.ActionID;

Its just a fast Example from my tests. Just by shure to order in the same way in the substring_indexes und use a good separator.

Have fun

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文