Order By Partial Field
Nov 13, 2006
How can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A
If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M
What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger
View 1 Replies
ADVERTISEMENT
May 6, 2005
I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field?
Thanks for any help.
Dennis
View 4 Replies
View Related
Feb 8, 2008
Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.
For instance, if you search for 'nny'
You might see a result of 'Johnny User'
Thanks.
J
View 4 Replies
View Related
Aug 6, 2007
How would I accomplish update just a part of a field? I have 500+ records in a table that got written as \share 01234.jpg, \share 01235.jpg...\share 01734.jpg but the records should have ended with .tif instead. Now the application cannot find the correct image because the extension is incorrect. How can I use the Update statement to just change the extension on these records?
Thanks.
View 3 Replies
View Related
Jan 12, 2006
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
View 2 Replies
View Related
Jun 11, 2001
Does ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
View 1 Replies
View Related
Jun 12, 2008
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View 8 Replies
View Related
Jan 4, 2006
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
View 2 Replies
View Related
May 23, 2006
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
View 1 Replies
View Related
May 19, 2005
Hi,
I have small question regarding ORDER BY clause.
I wanted some value of the fields should come first before other values….Something like:
+----------+----------------------------------------+
| code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
Thanks,
Firoz Ansari
View 1 Replies
View Related
Dec 30, 2005
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View 3 Replies
View Related
Apr 27, 2000
Hi,
How can I rearrange the fields in a table in SQL 7? If it is possible, will the existing data be lost?
Thank you for any help.
View 5 Replies
View Related
Apr 4, 2008
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table?
CREATE TABLE `Table1`
(
`table1_id` INTEGER AUTO_INCREMENT ,
`table1_name` VARCHAR(255),
PRIMARY KEY (`table1_id`)
)
CREATE TABLE `Table2`
(
`table2_id` INTEGER AUTO_INCREMENT ,
`table2_name` VARCHAR(255),
`table2_table1` INTEGER,
PRIMARY KEY (`table2_id`)
)
ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`);
What I'd like is something like:
SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name
It's the section in double quotes that I can't figure out how to compose.
As an example, if Table 1 has
1, A
2, B
3, C
and Table 2 has
1, a, 2
2, b, 1
3, c, 2
then I'd like the sort to return
2, b, 1
1, a, 2
3, c, 2
TIA,
Stephen
View 2 Replies
View Related
Jun 15, 2006
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View 13 Replies
View Related
May 27, 2008
Hi Guys,
Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field.
My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.
View 1 Replies
View Related
Nov 19, 2007
Hi there
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
Can someone help please.
Please ask if I haven't explained myself.
thanks
Dianne
View 9 Replies
View Related
Dec 20, 2006
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
View 1 Replies
View Related
Aug 21, 2007
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
View 7 Replies
View Related
Nov 18, 2014
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
View 17 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Apr 19, 1999
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records.
I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time.
I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410.
The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start.
This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
View 1 Replies
View Related
Sep 26, 2004
Hello - I was wondering if anyone knew how to do this -
I have a database with a field for Id, LName, GName, DOB. In the LName field, some of the names have * placed after the names. Is there a way I can search for the entries in LName with the * in the record?
Thank you!
Liz
View 3 Replies
View Related
Sep 30, 2007
Hi Guys,
I have started a project using Linq for sql and SSCE. Everything goes well until I realize that SSCE can only run in full trust. Linq for sql is planned to support partial trust scenario. Any plans for such a support in Sql Compact Edition?
Dany
View 1 Replies
View Related
Apr 12, 2008
The following code accepts a couple of parameters and creates a temp table to hold unpaid schedule rows (like invoices). Then is takes the payment amount passed and starts paying the oldest ones first, until it runs out of money or pays a partial. When it pays it inserts a row in the applieds table with the schedule_ID, Receipt_ID (payment id), applied amount, and applied date.
What I need help with is: Say there is a partial payment from a previous payment, I need to pay that one, but only the unpaid part. Then continue to pay other schedule rows...
I appreciate any help,
@PaymentAmount money,@PledgeID Int,@Receipt_ID IntAS-- Get unpaid reminder rows for the passed in pledge id and put them into the temp tableDeclare @temp_oldest Table(PledgeSchedule_ID int,ReminderDueDate datetime,AmountDue money)INSERT INTO @temp_oldest SELECT PledgeSchedule_ID, PledgeDueDate, AmountDueFROM tblPledgeReminderScheduleWHERE (dbo.tblPledgeReminderSchedule.Pledge_ID=@PledgeID) AND (dbo.tblPledgeReminderSchedule.ReceivedDate IS NULL) -- AND (dbo.tblPledgeReminderSchedule.PledgeDueDate < GETDATE())WHILE((SELECT Count(*) FROM @temp_oldest)>0)BEGIN-- If the payment is greater or equal to the amount due for the current row, do thisIF(@PaymentAmount >= (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN-- Update the reminder row with todays dateUPDATE tblPledgeReminderSchedule SET ReceivedDate = GETDATE()WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)-- Insert a row to track applied payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,AmountDue,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))-- Subtract the amountdue from the paymentamount and reset itSET @PaymentAmount = (@PaymentAmount - (SELECT Top 1 AmountDue FROM @temp_oldest))ENDELSEIF(@PaymentAmount < (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN -- Insert a row to track applied PARTIAL payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,@PaymentAmount,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))BREAKENDELSEIF @PaymentAmount = 0-- Delete all rows from temp tableDELETE FROM @temp_oldestELSE-- Delete only the current row from the temo tableprint @PaymentAmountDELETE FROM @temp_oldest WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)END
View 1 Replies
View Related
Jun 15, 2000
In our database we have the concept of 'Companies' each company has an entry in a tblCompanyControls using a field lCompanyNumber to uniquely identify it.
Company specific data is then grouped within additional tables using this
lCompanyNumber.
Thus all the departments for a particular company (eg 4) exist in a table
tblDepartments with lCompanyNumber =4. The same applies for pensions, pay
elements, employees and so on.
Each employee has various attributes stored in several tables, thus the
main data is stored in tblEmployees with an lCompanyNumber to illustrate
the company they belong to. And a system generated lUniqueID that is the 1
to many relationship to tables such as tblEmployeePayElements,
tblEmployeePensionSchemes.
These in turn have a primary key that establishes a 1 to many with the
period data for pay elements etc.
What we want to do is replicate only the data for a specific company, for
tblEmployees, tblDepartments this looks straightforward as i just set a
filter like 'tblEmployees.lCompanyNumber = 4'. My problem is how do I
replicate just the tblEmployeePayElements for those employees that are in
the specific company as the table does not contain the lCompanyNumber. Can
you replicate a view? I an quite ignorant of replication and would really
appreciate some pointers. Note DRI is not in use.
View 1 Replies
View Related
Sep 18, 1998
I am are running SQLSERVER SP4 on WINNT SP3.
I am serious problem of partial update my query is
something like
bEGIN tRAN
declare cursor...
SElect * from tableA where flag = null
open cursor
fetch first...
while @@FETCH_STATUS = 0
BEGIN
UPDATE TABLE tableB ..
.
.
.
fetch next
END
CLOSE ...
DEALLOCATE..
COMMIT TRAN
The problem is the cursor select retrieves
says about 10000 rows, goes thru the
loop for 1000 rows and just terminates without
giving an error message,or rolling back
in case of errors, but comes out as successfully
completed.
I am at loss as what could be the problems..
any suggestions welcome..
Thanks in advance,
Balajee.
View 2 Replies
View Related
Dec 13, 2007
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select *
into #temp_2
from #temp_1 a, Server2.DBa.dbo.table2 r
where r.field1r = a.field1a and
r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific:
r.field3r looks like 000884
a.field3a looks like 17445000884
So- I just want to pull in the 000884 off of a.field3a
View 2 Replies
View Related
Jan 30, 2006
Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".
i.e.:
rrb7@yahoo.com;richard.butcher@sthou.com;administr ator@sthou.com
etc like that.
each row varies with one exception. administrator@sthou.com is in each one.
is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?
thanks again for any easy answer
rik
View 7 Replies
View Related
Oct 2, 2013
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.
SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC
View 5 Replies
View Related
Feb 12, 2008
I have been asked to see what I can recover from a development server whose database became suspect during a power failure.
One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.
There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.
What are our options here?
Are their any good third-party tools to help us with this problem?
View 20 Replies
View Related
Jul 20, 2005
Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark
View 4 Replies
View Related
Jul 20, 2005
Hi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.
View 1 Replies
View Related
Jun 12, 2007
Hello All,
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
Thanks in advance for any help
-Clint
View 13 Replies
View Related
