How to select last record of group of records in SQL server

Question

0.00/5 (No votes)

See more:

C#

Hi everybody!
	I am sure this is pretty simple to do but I can’t do that. My problem :
I Have sql server data base with two table :1-parent_table   2-child_table
Parent table has 5 columns  as follow:    
Id    emp_num   group_num  edit_num  name_emp 
------------------------------------------------      
1     912230      1           1      James              
2     912230      1           2      James              
3     912230      2           1      David               
4     912230      2           2      David               
5     914350      1           1      John  
6     914350      2           1      Mark
.
.       
.         
Id column is primary key and   each  record of name_emp  has exclusive emp_num and exclusive group and may has many edition_num . each record in parent table is exclosive.  
For each records of parent table Exist many records in child table.
Id    emp_num   group_num  edit_num  name_emp  good  payment  date       discount
---------------------------------------------------------------------------------
1     912230     1         1        James     egg    100     20016/01/01  10
2     912230     1         1        James     pear   200     2016/02/01   15
3     914350     2         1        mark      egg    100     2016/02/02   10
.
.
.
How can  I  get  to latest  record of  (emp_num of X group of x edit) in child  table  for each record in parent table not base on date but i maen last record that inserted
For e.g  how can I select  id = 2 in chld table  and how can I   show  Count rows which discount &gt; 0  in lable.Text

Please help!
Thanks

What I have tried:

i try this query:

Use mydatabase
Select emp_num,group_num,edit_num,id=MAX(id)
From tbl_child
Group By emp_num,group_num,edit_num

it make group my detail table(child) successfully and show last record but only display 3 columns when I add other column to SELECT error show:
Column 'tbl_child.sh_ghab' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Posted 12-Apr-16 7:16am

rezaeti

Updated 13-Apr-16 7:06am

v6

Add a Solution

Comments

Richard Deeming 12-Apr-16 13:51pm

Are you using Microsoft SQL Server?

Which version?

By "last record inserted", do you mean the record with the highest ID, or something else?

rezaeti 12-Apr-16 13:53pm

hi thnaks for reply
yes I use sql server 2008
i mean last record that insert in table naturally i think the last record has highest ID

rezaeti 12-Apr-16 13:56pm

id field is identity

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Accepted Answer · 2016-04-12T07:52:00

Solution 1

SQL Server does not guarantee any particular order for record storage (vs. a flat model such as FoxPro). If you want record entry information you must make allowance for it in the table. If the [id] field is an IDENTITY field then it is always in sequential order, newest with the largest number.

So - all you need to do is write your query to JOIN the parent and child tables appropriately, and the use a WHERE clause (or HAVING clause, if necessary) where you request MAX(id) from the table you wish to use to select the most recent record.

Since you didn't give your actual query, and your "What have you tried" doesn't tell us anything, I cannot be more specific.

Posted 12-Apr-16 7:52am

W∴ Balboos, GHB

Comments

rezaeti 13-Apr-16 13:03pm

hi thanks for reply
i have this query:

Use mydatabase
Select emp_num,group_num,edit_num,id=MAX(id)
From tbl_child
Group By emp_num,group_num,edit_num

it make group my detail table(child) successfully and show last record but only display 3 columns when I add other column to SELECT error show:
Column 'tbl_child.sh_ghab' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

W∴ Balboos, GHB 13-Apr-16 13:26pm

The error is clear.
You'll need to add sh_ghab to your GROUP BY list.
or
If there's an appropriate aggregate function, wrap the sh_ghab column with that.

rezaeti 13-Apr-16 13:32pm

hi thanks for reply
but when I add sh_ghab to GROUP BY list all records display
I want to display last record of each group

W∴ Balboos, GHB 13-Apr-16 13:36pm

That's what the WHERE clause if for - you need to refine it so that it can determine which is the last (or, if required, a HAVING clause to do this filtering).

rezaeti 13-Apr-16 13:38pm

thanks

W∴ Balboos, GHB 13-Apr-16 13:41pm

Remember to close your questions by marking them answered if and when you feel they are answered.