sql select last record of detail table

Question

1.00/5 (1 vote)

See more:

Hello,
I have 2 tables in an SQL 2008 Server, master and detail, detail table has a date field, I want to check the last record's date field of the detail table of every master record. I want either to add the last record to a NEW datatable or add it to a grid so I can check with the date.now function if the date of each record meets some criteria eg. if from the date.now function date field value is a month forward : date.now = 18 July 2013 and date field value os 18 August 2013 then do something eg display a messagebox. I have made the code for the check date (if need I will post it in VB .Net) but how can I do the selection, is something like this work?

VB

For i = 0 To datatable.Rows.Count - 1
            combobox.Items.Add(datatable.Rows(i).Item("date").ToString)
        Next

maybe it is a dbgrid or something. OR can I use an inner join in an SQL string with an sqldataadapter and add the detail's table last record's datefield to a NEW dataset and do the check? if you can add some SQL code to do it?

CSS

master TABLE : Student
StudentId   StudentName
1           PRASAD
2           RAM
3           RAJ




detail TABLE : Enroll
EnrollId        Course      StudentId   RegesterdDate
1               JAVA        1           2014-07-05 09:03:22.330

2               .NET        1           2014-07-07 09:03:22.330

3               HTML        2           2014-07-01 09:03:22.330

4               MVC         2           2014-07-09 09:03:22.330 value I want to select the most recent date of the detail table this is one record of student RAM

5               CSS         3           2014-07-05 09:03:22.330

6               JQUERY      3           2014-07-06 09:03:22.330




Expected Result:
StudentId   StudentName     Course      RegesterdDate
1           RAM             MVC         2014-07-09 09:03:22.330

Thank you

Posted 10-Jul-14 8:03am

Member 3892343

Updated 10-Jul-14 22:34pm

v6

Add a Solution

Comments

Maciej Los 10-Jul-14 15:03pm

Not clear ;(

OriginalGriff 10-Jul-14 15:11pm

I had a think about this, and there isn't really enough info here to be precise.
Perhaps what you want to do is forget English for a few minutes and give us short samples of the two tables and what you want to see as the output - that way we can tell where what data is, and how it connects the two tables together?
Use the "Improve question" widget to edit your question and provide better information.

Swinkaran 11-Jul-14 0:00am

As per my understanding, you want to find out the Course the student recently enrolled to. Am I correct?

Member 3892343 11-Jul-14 4:38am

yes i want to select the molst recent date of the records of detail table of every master table record, i will try other answers sql also.

ArunRajendra 11-Jul-14 5:20am

Check my solution hope that's what your are looking out for.

2 solutions

Add a Solution

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

BacchusBeale · Answer 1 · 2014-07-10T18:13:00

Not sure if this what you mean. In my project I had to list the latest record with data from linked tables. My query was (with names changed), you can add other columns you need:

C#

string query = "select StartTimestamp from Table1, Table2 ";
       query += "where Table1.ID=Table2.ID ";
       query += "and StartTimestamp in (select MAX(StartTimestamp) from Table1)";

You could also use SELECT TOP command after sorting.

ArunRajendra · Answer 2 · 2014-07-10T20:42:00

Try this query.

SQL

select a.StudentId,StudentName,Course,RegesterdDate from Student a inner join Enroll  b on a.StudentId = b.StudentId
 Inner join (
 select StudentId,MAX(RegesterdDate) rdate from Enroll
 group by StudentId) as p on p.StudentId = b.StudentId and p.rdate = b.RegesterdDate

sql select last record of detail table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

sql select last record of detail table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us