Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-12-07 : 08:54:55

Hi,

I have the following select query which extracts data fine.

select prac_no, post_code, country from dbo.TblPracDetails
order by post_code

Results

prac_no post_code country
1 E2 1GX UK
2 E2 1XU UK
3 ME3 7DB UK
4 ME3 9HU UK
5 ME4 5JY UK

Now, I have another query as

select prac_no, post_code, country from gprdsql.TblPracDetails
where prac_no = 3
order by post_code

Result

prac_no post_code country
3 ME3 7DB UK

What I want to achieve is to update the second qury and obtain the Result,

prac_no post_code country 
2       E2 1XU     UK
3       ME3 7DB    UK   
4       ME3 9HU    UK

It reads prac_no "3" then it reads the top and bottom row and displays it.

Any help please

ladylovespeace
Starting Member

1 Post

Posted - 2010-12-07 : 08:56:52

Hi,

I have been battling with this question the whole day….

1. QUESTION

You want to display each employee's last name, hire date, and salary review date (which is the first Monday after six months of service), label the column REVIEW, and format the dates to appear in the format similar to "Monday, the Thirty-First of July, 2000."

Type the <missing code> in the TO_CHAR statement.

This SQL command displays the salary review date formatted to "Monday, the Thirty-First of July, 2000."

When you click Execute, the results show each employee's last name, hire date, and salary review date. It also labels the column REVIEW and formats the dates to appear in the format similar to "Monday, the Thirty-First of July, 2000." The TO_CHAR function is used to convert a date from its default format to the one that you specify.

MY ANSWER:

SELECT last_name, hire_date,TO_CHAR(NEXT_DAY (ADD_MONTHS(hire_date, 6),'MONDAY'),'FnDay, "the" fnDdspth "of" FnMon, YYYY') REVIEWFROM employees;

I have typed my answer in the pre-test on my pc and it tells me that the answer is incorrect.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 09:24:38

I have absolutely ny clue what the previos answer is about...is it spam? Can't see any relevant spam either. Weird.

However; am I right in saying that you would like the record before and after the one you are specifying? In that case *I think* this should do it (a little late in the day here):

with cte as (
	select 
	  prac_no, 
	  post_code, 
	  country,
	  RowNum = ROW_NUMBER() OVER (ORDER BY prac_no) 
	from dbo.TblPracDetails
	)
select b.*
from cte a
	inner join cte b
		on a.RowNum between a.RowNum-1 and a.RowNum+1
where a.prac_no = 3

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-12-07 : 09:33:21

its bringing everything not when ONLY prac_no = 3

Result

prac_no post_code country RowNum
1 E2 1GX UK 1
2 E2 1XU UK 2
3 ME3 7DB UK 3
4 ME3 9HU UK 4
5 ME4 5JY UK 5

I would like to have

prac_no post_code country RowNum

2 E2 1XU UK 2
3 ME3 7DB UK 3
4 ME3 9HU UK 4

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 09:39:32

Ah...typo! Try this one:

with cte as (
	select 
	  prac_no, 
	  post_code, 
	  country,
	  RowNum = ROW_NUMBER() OVER (ORDER BY prac_no) 
	from dbo.TblPracDetails
	)
select b.*
from cte a
	inner join cte b
		on a.RowNum between b.RowNum-1 and b.RowNum+1
where a.prac_no = 3

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-12-07 : 09:45:32

Thank you so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources