Select Records From Two Tables, Plus Page And Order Them?
Feb 7, 2008
Hi,
I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....
DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)
INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p
SELECT * FROM @Temp
ORDER BY Name ASC
I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?
Many thanks for any help! :)
View 10 Replies
ADVERTISEMENT
Jan 3, 2008
I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first,
I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order.
Is it possible to select records from a physical table into a temp table in the same order while creating a unique field?
Here is how they exist in the physical table:
CLIENTID CHANGEDATE NEWVALUE
------------ ----------------------- ----------------------------------------------------------------
C0HTRA200005 2007-11-19 21:11:57.000 NULL
C0HTRA200005 2007-11-19 21:11:57.000 NULL
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-21 06:46:17.000 Acquisition
C0HTRA200005 2007-11-22 07:47:56.000 Acquisition
Here is how they exist after being inserted into the temp table using DISTINCT:
ID CLIENTID CHANGEDATE NEWVALUE
----------- ------------ ----------------------- ----------------------------------------------------------------
15 C0HTRA200005 2007-11-19 21:07:20.000 NULL
16 C0HTRA200005 2007-11-19 21:10:52.000 NULL
17 C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
18 C0HTRA200005 2007-11-19 21:11:57.000 NULL
19 C0HTRA200005 2007-11-21 06:46:17.000 Acquisition
20 C0HTRA200005 2007-11-22 07:47:56.000 Acquisition
I've tried this many different ways using variations of ORDER BY/ no ORDER BY.
I've tried creating the temp table first with the IDENTITY field, then insert into it.
I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well.
Any ideas would be huge.
Thanks much.
View 5 Replies
View Related
Nov 29, 2007
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
Thanks!
CB
View 1 Replies
View Related
Aug 7, 2005
HiI'm using Access 2002. I have 2 tables tblGroupContact,tblGroupPermission, both have 2 fields identical structure:ContactID GroupID (Both are Composite keys and both hold integers)tblGroupContact holds everybody and the groups they are members of.tblGroupPermission holds only those people who have permission to makechanges to another part of the DB.The SQL at the end of post works, but opens a dialogue box looking fora parameter value 'query1.ContactID'Clicking enter or cancel (without entering anything) works OK.What have I done wrong to cause this parameter request.Thanks ColinKSELECT tblGroupContact.ContactID, tblGroupContact.GroupIDFROM tblGroupContact LEFT JOIN tblGroupPermission ON(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND(tblGroupContact.GroupID = tblGroupPermission.GroupID)WHERE (((tblGroupPermission.ContactI*D) Is Null) AND((tblGroupPermission.GroupID) Is Null));
View 3 Replies
View Related
Jun 1, 2001
Attached is my select query, but it is returning the database values twice.
Can anyone tell me how I tell it to stop! A sub query I guess....
SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry
View 4 Replies
View Related
Jun 18, 2015
i have 3 tables names parent, child1, child2 parent has 1 record, child1 has 2 record and child 3 has 3 records the script
select Parent.*,child1.f1,child2.f2 from child1 inner join Parent on parent.id =child1.id
inner join child2 on child1.id =child2.id
running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products
Object: Table [dbo].[Parent] Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[id] [int] NOT NULL,
[code]....
View 8 Replies
View Related
Jun 25, 2007
Hi, all experts here,
I encontered a problem which did not allow me to select any thing from the temparary tables of the tempdb database. (e.g one of the temparary tables is #239E4DCF), why is that and how can we solve this problem? As I urgently need to look at this tempdb database, it is full. I am looking forward to hearing from you and thanks a lot in advance for your help.
With best regards,
Yours sincerely,
View 1 Replies
View Related
Dec 12, 2007
I want to show more records in each page when i view report
Question is subject above
any idea? thanks
View 4 Replies
View Related
Mar 27, 2008
Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
View 5 Replies
View Related
May 7, 2015
I have a report with multiple tables. I need to show each tables in different pages. When there is no data for tables/tables , it is coming with the next table which has data. I have given "Add a page break after" option in the tablix but still the tables are coming together when no data available. How can I show it in different page?
View 2 Replies
View Related
Sep 6, 2007
lol, you still still didn't answer the most important questions about what you want the results to be, and whether the data is consistent.
View 1 Replies
View Related
Jan 24, 2005
yeah, you're right, it isn't totally clear: Quote: MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. i suppose "MS SQL" could be interpreted as "any product from microsoft that uses the sql language" but unfortunately what it really means is "microsoft sql server"
anyhow...
please show your latest query that doesn't work
View 8 Replies
View Related
Mar 14, 2007
hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0
Set strOrder = Request.QueryString("Order")
oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>
</head>
<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
make = oRS("make")
model = oRS("model")
price = oRS("price")
%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop
oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>
View 14 Replies
View Related
Feb 14, 2006
I am getting the following error in the database..
B-tree chain linkage mismatch
Keys out of order on page
I have run the DBCC Repair_rebuild with data_loss.
But this has not helped to resolve the issue. It is finding errors in the Primary Key Linkage.
Please assist.
Thanks for your help.
Karthik
View 20 Replies
View Related
Nov 17, 2006
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName
From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
Thanks,
Zoop
View 1 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Aug 15, 2006
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
View 3 Replies
View Related
Oct 1, 2007
I was able to fix this data with no data loss.
However How would I use a restored backup to find the records that were on this particular page?
DATE/TIME:10/1/2007 12:33:36 PM
DESCRIPTION:Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x00000003aa6000 in file 'K:Program FilesMicrosoft SQL ServerMSSQLDataMC_20070101_2.mdf'.
COMMENT:(None)
JOB RUN:(None)
You can do anything at www.zombo.com
View 7 Replies
View Related
Jun 14, 2007
Hi.
I want to know how many records there are per page.
For example there may be a textbox showing the count of records per page.
What is the expression for this?
Any help would be appreciated.
View 1 Replies
View Related
Oct 9, 2006
Using bcp to load data from a flat file into a database table will load the records in the exact same order as they are in the flat file; whereas, a DTS package may not load the records into the table in the exact same order as they are in the flat file. Why? ... the table has no keys or indexes ... the DTS package was created with MS SQL 2000 and is now being used in MS SQL 2005 ...
View 14 Replies
View Related
Sep 22, 2000
Hello,
I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.
SELECT * FROM QuestionMaster.
I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.
SELECT Convert(int,RAND()*97)
Thanks in a million.
Sarika
View 1 Replies
View Related
Oct 8, 2014
I have the below requirement:
1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.
docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4
[Code] ....
View 5 Replies
View Related
Dec 5, 2005
Is there a way I can specify how many records per page should be displayed?> This is a very basic functionality that needs to go in the deployment at any cost for us?...any ideas.
View 13 Replies
View Related
Jul 23, 2005
We have one single hash (#) table, in which we insert data processingpriority wise (after calculating priority).for. e.g.Company Product Priority Prod. QtyProd_Plan_DateC1 P11100C1 P22 50C1 P33 30C2 P11200C2 P42 40C2 P53 10There is a problem when accessing data for usage priority wise.Problem is as follows:We want to plan production date as per group (company) sorted order andpriority wise.==>With less data, it works fine.==>But when there are more records for e.g. 100000 or more , it changesthe logical order of dataSo plan date calculation gets effected.==Although I have solved this problem with putting identity column andchecking in where condition.But, I want to know why this problem is coming.If anybody have come across this similar problem, please let me knowthe reason and your solution.IS IT SQL SERVER PROBLEM?Thanks & Regards,T.S.Negi
View 5 Replies
View Related
Mar 13, 2006
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.
I have data in both table like
CompanyTable
ID Name
1 name1
2 name2
OrderTable
OrderId CompanyTabID
1 1
2 1
3 1
4 1
In my query I want to show all orders in single row.
ID Name Orders
1 name1 1,2,3,4
2 name2 null
Is anybody can help on it.
Thanks
Arvind
View 5 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 Replies
View Related
Apr 14, 2008
I have created a table and want to display a datset. One of the fields in the dataset is 'month' which I need displayed across the top of the report. The other information should make up the rows.
How do I get it to list the Month across the page rather than down?
View 7 Replies
View Related
Dec 22, 2006
Hi,
I use an embedded ReportViewer control as a LocalReport in a web form. The report displays the results in four pages and I need to display them all in one page (without any pagination). Is there a way to disable pagination? Also, what do I need to do to show the print button in toolbar?
Thanks,
Adarsh
View 3 Replies
View Related
Jul 26, 2007
Is it possible to set a page to display only certain number of
records at a time? Like 20 records per page?
thanks a lot
View 1 Replies
View Related
May 26, 2008
Hi,
I have few views in SQL Server 2005. In Design View, the results of View are ok. In OPEN VIEW option, records are not sorted correctly, ORDER BY is ignored.
What could be the reason for this ?
Thanks a lot in advance!
View 6 Replies
View Related
Feb 24, 2008
Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!
View 1 Replies
View Related
Jun 11, 2007
I upsized an access database with a key / index on ordernumber and linenumber.
However if I open the table in the Management Studio the records aren't ordered this way (same goes for select * from table) I get:
Ordernumber
Linenumber
200724001
37
200724004
3
200724006
33
200724001
3
200724011
19
200724014
5
200724006
37
200724011
19
200724006
28
Same goes for my crystal reports files, since the records aren't ordered by ordernumber / linenumber all my formulas go bezerk..
Am I wrong thinking the records should be ordered according to the prim. index?
Please help because I don't want to have to change all my 40+ reports to include an "ORDER BY"
Best regards,
Mike
View 7 Replies
View Related
Oct 7, 2015
I have a table that has the records as shown below
Id From To
1 Delhi Noida
2 Faridabad Kanpur
3 Noida Delhi
4 Kanpur Faridabad
5 Banglore Mumbai
6 G H
7 I J
8 Mumbai Banglore
I want the results in the following order
5 Banglore Mumbai
8 Mumbai Banglore
1 Delhi Noida
3 Noida Delhi
2 Faridabad Kanpur
4 Kanpur Faridabad
6 G H
7 I J
Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.
View 11 Replies
View Related