SELECT TOP 2 From Each Details Record

Apr 24, 2007

Hi everyone,

I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.


Example :
=======
Details table is as follows
HeaderID DetailsID
1 1
1 2
1 3
2 4
3 5
3 6
3 7
3 8


output should be:
TransDate SupplierName HeaderID DetailsID
1/1/2000 abc 1 1
1/1/2000 dsds 1 2
2/3/2003 fgd 2 4
2/4/2005 sdsd 3 5
1/1/2006 fgfdg 3 6


I am using the following query

SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
Header H, Details D
WHERE H.HeaderID = D.DetailsID
AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)


As I am dealing with very huge data it is taking very long time to execute.

Is there any better way to accomplish the task?

Thanks.

Riyaz

View 14 Replies


ADVERTISEMENT

Help Selecting Duplicate Record Details

Oct 10, 2006

I have the following query I am using to identify duplicate records in one of my database tables:


Code:


SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
GROUP BY memberID
HAVING ( COUNT(memberID) > 1 )



Executing the above proc returns 4079 records...

Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table...

I tried running the following procedure:


Code:

SELECT memberID,
COUNT(memberID) AS NumOccurrences, chapterID
FROM ChapterMembers
GROUP BY memberID, chapterID
HAVING ( COUNT(memberID) > 1 )



But zero results are returned ...

The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database.

Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated.

Thanks,

Jandrews

View 3 Replies View Related

Join Invoice Total Record To Details?

Oct 1, 2012

If you will I am trying to join a master invoice table to its detail records. The problem is I can't quite get the records to match correctly. There is a master record that has the net total of the invoice that corresponds to however many detail records for that invoice. I am attempting to get the records to line up in a query. I am having trouble because the key fields match the total up with each detail record. So for example in this record set below the 3825.75 value appears for each detail record so when I total the invoice column the figure is way too high. The detail has a 4462.54 and a -636.79 for a net of 3825.75. I tried to line the example up for better illustration. I copied it off a pdf and I am trying to replicate it programmatically.

0712RW-IN 7/31/2012 8/30/2012 4,462.54 0.00 3,825.75 INV 7/31/2012 4,462.54
C/M 8/31/2012 636.79- Reference: 0712RW
CREATE TABLE [dbo].[INVOICE](
[SRC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ARDIVISIONNO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CUSTOMERNO] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Code]....

View 1 Replies View Related

Select Customer Last Inserted Order Details

Sep 15, 2007

Hi,
Check this SQL


SELECT DISTINCT
TblOrder.CustomerUID,
TblOrder.OrderHiddenID,
TblPayment.PaymentAmount,
TblPayment.Result,
TblOrder.OrderID
FROM TblOrder
CROSS JOIN TblPayment
WHERE (TblOrder.CustomerUID = @IsCustomerID)
AND (TblOrder.OrderHiddenID = @IsHiddenID)
AND (TblPayment.Result = 'Pending')
AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))

one customer can have more than one orders.
So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this

View 4 Replies View Related

Replication :: Error Details Or Messages Of Select Session Not Shown

Jul 10, 2015

Since I had a bad error in replication, my replication monitor won't show the tab "Error details or messages of the select session". 

I am not able to activate or to show this "tab", to get all information about my error.

I added two screeshots, one with the 3 tab, and the other, were the information about my error are missing. How i can my default view back?

Replication Monitor OK:

My Replication Monitor with missing tab:

I had to know, which ID is causing a primary key violation. Instead of Replication Monitor, I would use t-sql.

View 3 Replies View Related

SQL Server 2012 :: Query To Select Parent Details From Child Table

Mar 3, 2015

I have a scenario,

We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.

Equipment table looks like :

Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177

There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.

Select query where i need to select the Code for all Parent_Id's.

View 8 Replies View Related

HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE

Dec 7, 2007

I have the following data

MASTER
id
name


DETAIL
id
master_id
name

I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View 1 Replies View Related

Can I Use SELECT Statement To Select First 100 Record????

Apr 21, 1999

I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View 1 Replies View Related

Select One Record Only

Nov 14, 2001

Hi

Here is my example: I have the following table

ID Name Phone
--- ---- -----
1 John 1234567
1 John H.
2 Dave 9876543
2 Dave Smith

I want to select only one record of each ID, the output should be

ID Name Phone
--- ---- -----
1 John 1234567
2 Dave 9876543

It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.

Thanks

View 4 Replies View Related

Trying To Select Last Record...

Nov 8, 2004

I have a sql query that looks for values in a few different databases....

is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?

View 2 Replies View Related

Select One Record

Sep 18, 2013

I have view with Patients name and Appointment table where I save those patients. How to create store procedure if I would like to select one patient record based on Patient_Id value?

View 3 Replies View Related

Using TOP To Select The 2nd Highest Record

Jun 19, 2007

Hai frendz,

I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-

"select top 1 EID, Salary from Employee ORDER BY Salary DESC"

Now I need to write a query which selects the second highest salary.
So how to achieve this?..

thanx

View 6 Replies View Related

How To Lock Record On Select

May 29, 2008

hi
I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.

View 1 Replies View Related

Select Only If There's A Same Record In The Table

Apr 26, 2005

i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.

View 1 Replies View Related

Select Date Record

Feb 18, 2006

    how to select record from the table where the data between a  range. example  between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks

View 2 Replies View Related

How To Select First Record With Out Using Rowcount

Mar 20, 2002

Hi,
I need to select first record from a table.
This can be comfortably achieved by usibg set rowcount 1.
I need to do this with out using rowcount.

This is urgent.
Thanks
Krishna

View 2 Replies View Related

Select Next And Previous Record

Aug 28, 2001

Hi!

I need to select a spesific record using the recordkey and then select the previous and the next record as well. (which leaves me with a recordset containing three records)

Is this possible?

View 3 Replies View Related

How Can I Select Nth Record In A Table.

Nov 7, 2001

hi friends,

I am having a table with student marks in that i need to select a student who is the 5 th rank based on total marks obtained by the student.how can write sql for that.

name, totmarks
-----------------
kumar 145
ravi 300
jude 189
geeetha 320
rajesh 251
guru 190
ramesh 99
----------------------

i am having records like this.pl help me. thank u

regards
raj

View 1 Replies View Related

How To Select Record Only In One Table?

Feb 14, 2006

Hello, everyone:

I have two tables like:

Table A
IDCodeANumA
1D1289
2C4300
3C9409

Table B
IDCOdeBNumB
1D3266
2C4300
3C7101

How to find out the records that is in Table A not in Table B, and in Table B not in Table A? That means C4/300 should not be selected.

Thanks

ZYT

View 3 Replies View Related

How To Select One Record Many Times?

Sep 14, 2006

The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.

Any suggestion will be appreciated.

ZYT

View 4 Replies View Related

Select Most Recent Record

Dec 21, 2006

Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.

Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00

All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.

I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01

We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o

This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3
FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))

View 1 Replies View Related

How To Select Earliest Record

Mar 17, 2012

im trying to get the earliest record. data is below

note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332/ 20090106<---- / 20081219/ 20090106
2332/ 20090323/ 20081219/ 20090323
2332/ 20090413/ 20081219/ 20090413
2332/ 20090507/ 20081219/ 20090507

because the bankruptcy_date date are all equal i would need to pull one record with the earliest date from the doc_received_date. The date with the arrow is the record i want. So basically i would like to show this result

note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332 20090106 20081219 20090106

View 5 Replies View Related

Select Last Record By Group?

May 18, 2012

query to show last record/Partner or PartnerName.

select 'PURCHASE' as EntityName, d.PartnerName, h.*
from (
select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
from DataImport.PurchaseHistory
group by DistributorId, StartDate, EndDate
) h
inner join Partner d on d.PartnerId = h.DistributorId
where d.Active = 1
order by DistributorId, StartDate desc, EndDate desc

View 4 Replies View Related

Select Max Record With A Condition

Sep 17, 2012

I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other

IDTimestamp
1NULL
209/17/2012 11:04:32
309/17/2012 11:05:44
409/17/2012 11:05:45

So I need a query to return

IDTimestamp
409/17/2012 11:05:45

as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.

View 6 Replies View Related

SQL Select Last Record Per Group

Jan 24, 2004

I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.

TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3

TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1

I need a view or SP to return the following:

ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1

It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx

View 2 Replies View Related

Select Nth Largest Record - How To?

Feb 3, 2004

How do i select first name of the lets say 6th highest salary of an employee?

is it use Select TOP statement?

p/s: I just want the 6th largest fname not the top 6. so it will only return one value.

Thank you in advance for your reply.

View 1 Replies View Related

Select Most Recent Record??

Apr 27, 2004

Hi,

I want to construct a query that would give the most recent record. Table data looks like -

F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/6/2004 9:31:54

All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent.
If data is like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/7/2004 19:31:54

Select the 2nd record.

How do I construct the SQL query?


Let me know.

View 1 Replies View Related

Select And Display Only One Record

Sep 18, 2013

I have view with Patients name and Appointment table where I save those patients. In form I have 2 comboboxes. For PatientComboBox source is store procedure based on Patient view. For PatientAppintmentComboBox I would like to create store procedure.

How to create store procedure to display only one PatierntName record in PatientAppointmentComboBox if Patient_Id selected from PatientComboBox exist or not exist in Appointment?

View 3 Replies View Related

Select One Record From Each Group

Jan 21, 2014

I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?

View 1 Replies View Related

How To Select Last Record In Table

Oct 15, 2007

Hey guys, is there an easy way to select the very last record in a table?

View 5 Replies View Related

How To Select Specific Record.

Oct 24, 2007

Group_Code *****Station_nbr*****Beg_Eff_Date*****End_eff_date

00000002 D01G00733 1/1/2007 8/31/2007
00000002N D01G00733 4/1/2007 8/31/2007
00000002W D01G00733 1/1/2007 12/31/2007


For the report I just want to be able to pick up the first and the last line.

Using dbo.Station_group Table

Thank you for the help

View 14 Replies View Related

DTS Select Using Record Position

Jul 20, 2005

Hi there, it has been a while since i have posted. I am in asituation where I am stumped. I am learning to build a dts packagewhere I am connecting to a table in an AS400. This database is beingmaintained by an outsourced company and therefore I can't change thetable structure or even ask them to. Anyway, this table currently hasabout 104,000 records. I am building a package to check it and pullout the most recent records and put them where they go in my SQLServer 2000 tables. The only way I can think of to get the mostrecent records is to use a global variable in the package to rememberthe record count and then get those records from that record positionon. Problem is, I have no idea how I would go about selecting recordsfrom a record position. Does anybody have any ideas or should I beusing a different approach? There are no time stamps to work from. Iwas told that the AS400 records, including updates, are appended tothe table, which is why I thought this approach made sense. I wouldtruly appreciate any help.

View 1 Replies View Related

Select Bottom Record

Feb 19, 2008

Hi Guys, I am trying to select the bottom record in a tabel based on a primary key and having no luck. Can some please help. This is how I have tried.


"SELECT Bottom Week,DOR from Observation_Record Where ID = '" + ID + "'"Thanks Trafmore

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved