SQL Tutorials

SQL Server 2012: OFFSET-FETCH

This is the first in a number of articles I intend to write whilst studying for my MCSE, so that I can not only consolidate the things I’ve learned, but also to share stuff with you guys.

The OFFSET-FETCH clause was new to SQL Server 2012 and provides a different means of filtering data. OFFSET-FETCH can provide some similar functionality to the TOP clause, but it has some particularly useful applications in your T-SQL queries.

The syntax looks something like this:-

OFFSET x ROW/ROWS FETCH FIRST/NEXT x ROW/ROWS ONLY

It might look something like this in practice:-

OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

So, what does it do?  Well it actually has two parts.

  • The first part is OFFSET x ROW/ROWS.  This part ‘skips’ the first x rows that the query would normally return, and then returns the rest.  This part can be used by itself, without using FETCH.
  • The second part is FETCH FIRST/NEXT x ROW/ROWS.  This part returns the next x rows after the OFFSET portion of the clause.  This can only be used if OFFSET is in use.

So, for our example above (OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY) the return would be the first 10 rows.

Before I go into a couple of other examples, there are a few things worth pointing out:-

  1. The OFFSET-FETCH clause requires ORDER BY to work, and must follow ORDER BY (in fact,some people see it as a part of ORDER BY).  Without ORDER BY, it won’t work.
  2. You can use either the word ‘ROW’ or ‘ROWS’ in either part of the clause with no difference in output.  They are completely interchangeable and it’s literally down to you how you want to use them – I like to switch them around to aid readability of the query.
  3. Similarly, you can use FIRST or NEXT in the second part of the clause and it doesn’t make any difference to the query output – again use whichever aids readability.
  4. Unlike TOP(x) there is no functionality for you to return a percentage of rows.
  5. You can’t use TOP(x) and OFFSET-FETCH at the same time!  Use one or the other.

To top it off lets look at a couple of examples using the AdventureWorks2012 database.  For each I’ll provide an example and an explanation of how the OFFSET-FETCH clause will affect the query output,

Example 1

SELECT [AddressLine1]
 ,[AddressLine2]
 ,[City]
FROM [AdventureWorks2012].[Person].[Address]
ORDER BY AddressLine1
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

This example skips the first 10 rows, and then returns the next 20.  For the following examples the query should be the same, the only part that will change is the last line containing the OFFSET-FETCH clause.

Example 2

...rest of query...
OFFSET 30 ROWS

This example would exclude the first 30 rows of the query.  If without this your query would normally return 90 rows, you would get the last 60 by using this.

Example 3

...rest of query...
OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY

This would return only the first row of the query

Example 4

...rest of query...
OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

Whilst some of the text might look different, this would give you exactly the same result as example 3.  Remember, the word ROW can be replaced with ROWS and the keyword NEXT and FIRST are also interchangeable.

One of the most useful applications of this clause is to provide paging for your data.  For example, you might want page one of a report to show records from 1 to 50:-

OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY

and then in page 2, the next 50 rows:-

OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY

And so on…quite useful, isn’t it?

 

2toria Web Links

2toria favourites – week ending 23/02/2014

Here are some of our favourtie web things from over the past week!  Enjoy!

Random

2toria favourites – week ending 16/03/2014

Here’s this weeks list of some stuff we’ve been enjoying at 2toria towers:-

Random

2toria favourites – week ending 09/03/2014

We’ve been enjoying some pretty good stuff on the web this week, here’s a few links (sorry for the small amount – more next week, I promise):-

Random

2toria favourites – week ending 02/03/2014

Not done this for some time, so I thought I’d start it up again.  Here are a few of the things we’ve been looking at, enjoying and favoriting on 2toria this week:-

2toria

No Years Resolutions

Resolutions, resolutions, resolutions.  They’re a pain in the arse, aren’t they?

Last year I promised myself (and you) that I would not procrastinate, that I would post at least twice a week, and lots of other false promises…  I did manage to get a few things done, but nowhere near as much as I’d planned at the beginning of the year (only 30 posts in 2014…poor effort).

So, I’m not doing that this year, at least not publicly.  I do have plans for the site and some other exciting projects during this year, but for now I’m keeping them in my head!!  In the meantime please keep on coming back, and I’ll try to keep you entertained more during 2014.

Happy New Year, by the way.

Tutorials Web

Getting to grips with Emmet – part two

Emmet is a useful bit of kit that allows you to quickly create HTML markup by using CSS-style abbreviations.  A couple of days ago I posted an introduction to the syntax of Emmet and asked you what the basic Emmet code would be for the following:-

<div>
  <h1></h1>
  <p></p>
  <h2></h2>
  <p></p>
</div>

Did you get it in the end? The answer I came up with is this:-

div>h1+p+h2+p

Try it now in codepen, jsbin or jsfiddle and after typing press the tab key and the HTML markup should appear as shown earlier.

In today’s post I want to cover a couple more handy hints on using Emmet to speed up your HTML coding.  The first thing I want to cover is producing code like this:-

<ul>
  <li></li>
  <li></li>
  <li></li>
  <li></li>
  <li></li>
  <li></li>
</ul>

Fairly straightforward using what we know already, isn’t it?

ul>li+li+li+li+li+li

BUT, it’s still a bit of a pain to type in, and counting those li elements is a bugger, too.  What if we had twenty list items, or thirty, or even more?  Turns out Emmet lets you do things in a much simpler way.  You just tell it how many elements you want using the multiplication symbol (*):-

ul>li*6

How simple is that?  Amazing, I say.  Using this technique you can easily create as many elements as you like!  Brilliant..

So, to make something like this:-

<div>
  <p></p>
  <p></p>
  <p></p>
  <ul>
    <li></li>
    <li></li>
    <li></li>
  </ul>
</div>

You can now write this:-

div>p*3+ul>li*3

Not too complicated at all, is it?

One more hint for today before I sign off.  How do you think you would do this?

<div>
  <p></p>
  <p></p>
</div>
<div>
  <p></p>
  <p></p>
</div>
<div>
  <p></p>
  <p></p>
</div>

It’s quite a tricky one and (as far as I’m aware) isn’t possible with the basics I’ve taught you so far, because all we really know at the moment is to nest items or make them siblings of each other.  What we can see from the example is that we are looking at three adjacent divs, each with two paragraphs in it.  To pull of something like this we need to group things together, and we do that using parentheses (brackets to you and I).

Grouping items means you can apply the same kind of syntax, but have things together as one.  Consider this:-

<div>
  <p></p>
</div>
<div>
  <p></p>
</div>

Using parentheses, you could do this:-

(div>p)+(div>p)

Try it now.  Magic eh?  This has said that we want the div>p to be grouped together and then made into a sibling of div>p.  The brackets bring the elements together so that Emmet treats them as individual.  Now, given that we know how to specify how many times an element is created, we can combine what we know into this to get the result we need:-

(div>p)*3

Again, magic!  Parentheses can be used to group items together, and can even be nested to make much more complex structures than we could at the start of this article.  Try the example below for size (after you’ve tried to figure out what the outcome will be):-

(div>p)+(div>ul>li*5)+((div>h2>p)*2)*3

(Ok, it’s overcomplicated, but you get the idea).  In part three of this series of articles I’ll be looking at some more hints, including adding id and class attributes to your elements.  Come back soon!

CSS Web Links

Tridiv – In-browser CSS 3D Editing

I’m a sucker for clever web things, and today’s link is no exception.  Tridiv is an interface where you can create 3D shapes in CSS, and all without knowing how to make 3D shapes using CSS at all.

tridiv main

The interface is extremely cleverly put together, and literally allows the user to create 3D objects as they might be used to in some other 3D editing interfaces.  There’s a top, front and side elevation, and a handy window for you to move and rotate your newly created object.

tridiv interface

Basic primitive shapes are included in the mix (cuboid, cylinder, prism and pyramid), which you can add to the scene, after which you can rotate, enlarge, and move to your hearts content.  Colors and opacity can be manipulated too, giving you the means to make some pretty spectacular 3D shapes if you wish.  Lighting, shadows and images can also be added to your creations, giving you the ability to make some pretty impressive things.

Once done, you can save for use elsewhere, or even export to Codepen to share with others.

Some have criticised the project based on its practicality, but I personally think it’s a fun little tool that really demonstrates how far the web has come along.  And it was made by just one person!

Only downside is that it currently only works in Chrome, but Julian Garner has claimed he is working on versions for other browsers soon.

To really see what could be done with it, check out this demo on Codepen, created by the developer himself!

Tutorials Web

Getting to grips with Emmet – part one

Quicker (and clever) ways of doing things always impress me.

HTML can be a bit of a pain to write out, especially if you’re building the skeleton for a webpage or website.  Emmet is a very handy tool that makes building the basic framework of HTML an easier, quicker task.  This is the first of a number of tutorials the basics of what it is and how to use it.  There’s actually quite a lot to Emmet and how it works, so I’m going to start with the basics.

emmetlogo

Emmet is ‘a web-developer’s toolkit that can greatly improve your HTML & CSS workflow’, to quote the developers website.  It exists as a plugin to text editors, and can also be found on some of the online web editors (such as jsfiddle, codepen and jsbin).  In the examples that follow I’m going to use codepen, but it’s up to you which you want to use.  You could use your own IDE, but installing, configuring and using can differ, so for now if you’re new I’d suggest following along in one of the web-based editors so that the series makes sense.  I’ll cover using it as a plugin at some point in the future.

So, how does it work?

Take the following HTML structure as a simple example:-

  <body>
    <div>
      <p></p>
    </div>
  </body>

Emmet lets you specify HTML structure in an quick, abbreviated and simplified way,  and automatically expands to the full HTML structure. It’s kind of like using code snippets if you’ve used them before, only very quick and very cool.

Using Emmet abbreviations, the code above would look like this:-

body>div>p

Let’s try it now.  Open up codepen and in the HTML section type the code directly above and press the tab key.  As if by magic your HTML structure will appear, indented and everything.  Isn’t it cool?

Now, try this:-

body>div>ul>li+li+li+li

Once again, press the tab key directly after.  You should now get this:-

<body>
    <div>
      <ul>
        <li></li>
        <li></li>
        <li></li>
        <li></li>
      </ul>
    </div>
</body>

Great, isn’t it?  But what exactly is going on?  Let me explain.  If you know advanced CSS selectors, you might already have figured it out, but here goes.

The examples so far use two symbols, > and +.  The greater than symbol denotes nesting (and the parent/child relationship), so in this example:-

div>p

We are saying we want a paragraph element within a div.  This:-

div>ul>li

says we would like a div, which contains a unordered list, which in turn contains a list item.

Simple so far?  Now for the plus symbol, which asks that the elements surrounding it are adjacent to (or siblings of) each other, so:-

li+li+li+li

would give you

<li></li>
<li></li>
<li></li>
<li></li>

And the following:-

div>h1+p+div>ul>li

Would give you:

<div>
  <h1></h1>
  <p></p>
  <div>
    <ul>
      <li></li>
    </ul>
  </div>
</div>

Does that make sense?  This is the most basic of Emmet syntax, and that’s where I’m going to leave you for today.  Try it out in codepen and see what you can come up with.

Next time around I’ll go into more detail on more advanced markup you can create with this handy little tool.  Until then, as a challenge try and come up with the Emmet syntax for the structure below:

<div>
  <h1></h1>
  <p></p>
  <h2></h2>
  <p></p>
</div>
Web Development Web Links

Beautiful parallax effects

Parallax effects on webpages can be awesome if done well.   I’m still pretty new to js and web development in the grand scheme of things, so haven’t figured out how to do them yet (I will though), but I think they’re very cool.

parallax

Parallax.js by Matthew Wagerfield is a ‘simple, lightweight parallax engine that reacts to the orientation of a smart device’, and, based on the demo here, it’s pretty impressive.  The graphics and animation used are beautiful and if you run it on your iPad or iPhone and move it about, the effects are pretty spectacular.  Do follow the link, as the picture above doesn’t do justice.  If you don’t have a mobile device you can move your mouse about to get an idea of the kind of effects you’ll get with motion.  It kind of reminds me of the effects in Little Big Planet.

I’d love to put something together like this one day.  The project GitHub page is here.